Sequences
- 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');