Sequence: generate unique numbers, useful for primary key values.
Is a named separate object (not a datatype) in schema, not associated
with a table.  Can be used anywhere; editable.

CREATE SEQUENCE sequencename [START WITH n][INCREMENT BY m];

CREATE SEQUENCE pet_seq;


--to see sequences
SELECT * FROM user_sequences;

--use NEXTVAL pseudo-column to retrieve next sequence value.
--must use sequence name.  each use of NEXTVAL increments.
INSERT INTO table VALUES(sequencename.NEXTVAL,...);

--suppose Pet has a column: seqval Number Primary Key
INSERT INTO pet VALUES(pet_seq.NEXTVAL,'Cleo','Bob','dog','f','3-Jan-2004',null,12,80);
--(re)set all rows seqval column
UPDATE pet SET seqval=pet_seq.NEXTVAL;

--SQL*Loader can generate unique sequential key values in specified columns when loading text file.

--value is never re-generated; Rollback does not reset.

--CURRVAL pseudo-column retrieves same value. 
--useful for insertions into dependent tables.


NOCACHE to avoid gaps in sequence of numbers that would result if cached values 
are lost when server shutdown.