Re: Can this sequence be done in Oracle?

From: Ian Ledzion <ian.ledzion_at_xlgbow.com>
Date: Mon, 26 Mar 2001 13:45:41 +0200
Message-ID: <99na5d$2kk$1@rex.ip-plus.net>

Run the script below. What it does is create a table with a sequence number by date, and a package with one external function which returns a sequential number for the date sent to it.

/*Begin script*/

CREATE TABLE test_date_seq
 (

  tds_date                   DATE NOT NULL,
  tds_lastnum                NUMBER(12) DEFAULT 0 NOT NULL

 )
/

ALTER TABLE test_date_seq
 ADD CONSTRAINT test_date_seq_pk PRIMARY KEY (tds_date)  USING INDEX
/

/* Package Spec for sequence */

CREATE OR REPLACE PACKAGE test_date_sequence IS

   FUNCTION get_date_sequence (p_date IN DATE)

      RETURN NUMBER;
END;
/

/* Package Body for sequence */

CREATE OR REPLACE PACKAGE BODY test_date_sequence IS

   /* Private functions + procedures */
   FUNCTION date_row_exists (p_date IN DATE)

      RETURN BOOLEAN;    PROCEDURE insert_date_row (p_date IN DATE);

   FUNCTION get_next_seq (p_date IN DATE)

      RETURN NUMBER;    /* Sequence function */
   FUNCTION get_date_sequence (p_date IN DATE)

      RETURN NUMBER
   IS

      v_date DATE;
   BEGIN
      v_date := TRUNC (p_date, 'DDD');

      IF NOT date_row_exists (v_date)
      THEN
         insert_date_row (v_date);
      END IF;

      RETURN get_next_seq (v_date);

   END;    /* Private function + procedure code */    FUNCTION date_row_exists (p_date IN DATE)

      RETURN BOOLEAN
   IS

      v_date_count NUMBER;

      CURSOR date_row
      IS
         SELECT   COUNT (*)
         FROM     test_date_seq
         WHERE    (tds_date = p_date);
   BEGIN
      OPEN date_row;
      FETCH date_row INTO v_date_count;
      CLOSE date_row;

      IF v_date_count > 0
      THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;

   END;    PROCEDURE insert_date_row (p_date IN DATE)    IS
   BEGIN

      INSERT INTO test_date_seq
                  (tds_date)
           VALUES (p_date);

   END;    FUNCTION get_next_seq (p_date IN DATE)

      RETURN NUMBER
   IS

      v_sequence_number NUMBER;

      CURSOR date_row
      IS
         SELECT   tds_lastnum
         FROM     test_date_seq
         WHERE    (tds_date = p_date);
   BEGIN
      OPEN date_row;
      FETCH date_row INTO v_sequence_number;
      CLOSE date_row;
      v_sequence_number    := v_sequence_number + 1;

      UPDATE tpms.test_date_seq a
         SET a.tds_lastnum = v_sequence_number
       WHERE (a.tds_date = p_date);

      RETURN v_sequence_number;

   END;
END;
/

/*End script*/

"Nick Tentomas" <Ntentoma_at_CSBFACMGT.CSBFM.DAL.CA> wrote in message news:3ABB7204.CC5DC66F_at_CSBFACMGT.CSBFM.DAL.CA...

> Hi ,
>
> I need to create a sequence that will reset it self every day:
>
> For example when it is executed for a certain data it will start from
> 1..and then always add 1.
>
> If execute it for the day after it will start again from 1..and then
> always add 1.
>
> I have a table where part of the key is the a date and a sequence
> number. The application was created in mysql and I was created the
> sequences manually there but now I am hoping to use Oracles features to
> acomplish this.
>
> Thanks in advance,
>
> Nick
>
>
>

Received on Mon Mar 26 2001 - 05:45:41 CST