my notes...

Features :

• Typically used to generate Primary Keys

• Automatically generates unique numbers
• Replaces application code
• Sharable
• Speeds up access speed when cached in memory
• View sequence values in USER_SEQUENCES data dictionary.

Note : A sequence should be named after its intended use for clarity. However it can be used anywhere irrespective of its name.

Syntax:

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

DROP SEQUENCE sequence_name;

INCREMENT BY : default sequence increment is by 1.

START WITH : default sequence starts with 1.

NOMAXVALUE : 10^27 for ascending sequence, -1 for descending sequence.

NOMINVALUE : 1 for ascending sequence, 10^26 for descending sequence.

CACHE : how many values Oracle preallocates & keeps in memory. default is 20.

USER_SEQUENCES data dictionary:

SELECT sequence_name, min_value, max_value, increment_by, last_number

from USER_SEQUENCES;

Note : The last_number displays the next available sequence number unless there are cached values.

Pseudocolumns NEXTVAL & CURRVAL:

• sequence.NEXTVAL : returns the next available sequence value.

• sequence.CURRVAL : returns the current sequence value.
  

• Note : NEXTVAL must be issued before CURRVAL contains any value. If cache is disabled, than the next available sequence can be seen in USER_SEQUENCES without using NEXTVAL first.

Rules for using NEXTVAL & CURRVAL

Can be used in:

• VALUES clause of an INSERT statement.

• SELECT list of a Sub-query in an INSERT statement

• SET clause of an UPDATE statement

• SELECT list of SELECT statement not part of a Subquery

Can't be used in:

• A SELECT list of a View

• A SELECT statement with DISTINCT

• A SELECT statement with GROUPBY, HAVING, or ORDER BY clause

• A subquery in a SELECT, DELETE, or UPDATE statement

• DEFAULT expression in a CREATE TABLE or ALTER TABLE statement

Note : CACHE is populated the 1^st time the sequence is referred. NEXTVAL requests pull the values from the cache until the last value is used up, after which the next NEXTVAL request pulls another cache of sequences into the memory.

Gaps in Sequences:

• A rollback occurs. (Sequence generation is independent of commit & rollback.)

• System crash (Cached values in the memory are lost.)

• Sequence used in another table (same sequence used in multiple tables will lead to gaps in all those tables)

Altering Sequences:

• Only future sequence numbers are affected

• START WITH can't be altered. DROP & CREATE to sequence again to start it at a different number.

• Some Validation are performed (such as setting MAXVALUE lower than current sequence

• number not permitted).

• One must have the privileges to change the sequence.

Usage Example:

CREATE SEQUENCE departments_seq2

START WITH 20

INCREMENT BY 20

MAXVALUE 999

NOCACHE

NOCYCLE;

ALTER SEQUENCE departments_seq2

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

INSERT INTO departments (department_id, departments_name, location_id)

VALUES (departments_seq.NEXTVAL, 'Navigation', 9152);

SELECT departments_seq.CURRVAL FROM DUAL;