www.bullraider.com

Sequences

AddThis Social Bookmark Button
  •  Sequences are generally used  to  generate automatically primary key values.
  • Once a sequence is created, we can access its values in SQL statements with the CURRVAL and NEXTVAL pseudocolumn
  • CURRVAL which returns the current value of the sequence  or the NEXTVAL  which increments the sequence and returns the new value  

Creating a Sequence: Example The following statement creates the sequence dept_seq

CREATE SEQUENCE dept_seq
 START WITH     1
 INCREMENT BY   1

 MAXVALUE 50
 NOCACHE
 NOCYCLE;

START WITH  Specify the first sequence number to be generated.

INCREMENT BY: Specify the interval between sequence numbers.

MAXVALUE : Specify the maximum value the sequence can generate.

MINVALUE :Specify the minimum value of the sequence.

CYCLE :Specify CYCLE means  the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE :Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

CACHE:Specify how many values of the sequence the database preallocate and keeps in memory for faster access

Using Sequence:

CREATE SEQUENCE num_seq START WITH 10

select ORDERS_SEQ.NEXTVAL FROM DUAL;

INSERT INTO  emp
(emp_num, name)
VALUES (num_seq.nextval,'Alex');


Powered by Bullraider.com