Oracle SequencesOracle Sequences• Sequences are an independent object in the database (not a data type)•Sequences have a name and can be used anywhere a value is expected.•Sequences are not tied to a table / column.•Sequences generate a numeric value that can be assigned to any column in any table. •The table attribute to which you assigned a value based on a sequence can be edited and modified.•Sequences can be created and deleted at any time

Sequence SyntaxSequence Syntax

CREATE SEQUENCE name[START WITH n][INCREMENT BY n][CACHE|NOCHACHE]n = can be positive or negativeStart with : initial value. The default is 1Increment by: determines the value by which the

sequence incremented. Default value =1 can be + / - to ascend / descend

CREATE * FROM USER_SEQUENCES;

To check all sequences created : SELECT * FROM USER_SEQENCES;To use sequences during data

entry: You must use two special pseudo

columns: NEXT VAL ,

CURRVAL

INSERT INTO CUSTOMERVALUES(CUS_CODE_SEQ.NEXTVAL,’Connery’,

’sean’, NULL, ‘615’,’898-2008’, 0.00); CUS_CODE_SEQ.NEXTVAL: retrieves the

next available value from the sequence.Each time you use NEXTVAL, the

sequence is incremented.Once a sequence value is used it cannot

be used again.If your statement rolls back the

sequence value will not. The next time you use NEXTVAL it will look as if it skipped a number.

You can issue an INSERT statement without using the sequence.

CURRVALCURRVALretrieves the current value of a retrieves the current value of a sequencesequence

That is the last sequence number used.You cannot use CURRVAL unless a

NEXTVAL was issued previously in the same session.

The main use for CURRVAL is to enter rows in dependent tables e.g: INVOICE and LINE table are related in 1: M relationship through INV_NUMBER.

You use the INV_NUMBER_SEQ sequence to automatically generate invoice numbers. Then , using CURRVAL, you can get the latest INV_NUMBER USED and assign it to the related INV_NUMBER foreign key attribute in the LINE table.

Example:INSERT INTO INVOICE

VALUES(INV_NUMBER_SEQ.NEXTVAL, 20010,SYSDATE);INSERT INTO LINE VALUES(INV_NUMBER_SEQ.CURRVAL,1, ‘13-Q2/P2’, 1):

To Drop a sequenceTo Drop a sequence

DROP SEQUENCE CUS_CODE_SEQ;

REMEMBER

The use of sequences is optional. You can enter the values manually

A sequence is not associated with a table.