·  SQL – Sequence

 

- Info:

  - Sequence simply generates incremental sequence of integers.

 

- Sytax – Create:

  CREATE SEQUENCE mySequence

  START WITH   50     -- First integer will be 50.

  INCREMENT BY 10     -- Next integer is generated by adding 10 to previous one.

  MAXVALUE     9999   -- After max value is reached behaviour depends if CYCLE or NOCYCLE is set.

  NOCACHE             -- Do not precalculate integeres in advance.

  NOCYCLE;            -- After max value is reached invalidate sequence.

 

- Sytax – Change:

  ALTER SEQUENCE mySequence

  INCREMENT BY   10

  MAXVALUE       9999

  CACHE          10   -- Oracle preallocates in memory 10 integeres for faster retrievel.

  CYCLE;              -- After max value is reached start from initial value.

 

- Sytax – Create:

  DROP SEQUENCE mySequence;

 

- Sytax – Get Next Value:

  SELECT mySequence.NEXTVAL FROM DUAL;

 

- Sytax – Get Current Value:

  SELECT mySequence.CURRVAL FROM DUAL; -- NEXTVAL must be called first before CURRVAL can return value.

 

- Example:

  INSERT INTO PEOPLE (ID,                 ID_COPY           )

  VALUES             (mySequence.NEXTVAL, mySequence.CURRVAL);