Re: Can this sequence be done in Oracle?

Thanks a lot !!!

I will give it a try.

Regards,

Nick

Ian Ledzion wrote:

> 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 - 08:15:28 CST