Snippet Name: SEQUENCE: Demo code and Examples
Description: In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
Once you've created a sequence object, to retrieve the next value in the sequence order you need to use NEXTVAL.
Also see:
» SEQUENCE: Find sequence MAX value
» SEQUENCE: Reset a sequence to a predef...
» SEQUENCE: get sequence value into vari...
» SEQUENCE: Autonumber into two tables
» SEQUENCE: Create sequence demo
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 14th, 2009
|
CREATE SEQUENCE <sequence_name>
INCREMENT BY <integer>
START WITH <integer>
MAXVALUE <integer> / NOMAXVALUE
MINVALUE <integer> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;
-- basic autonumber with sequence:
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.NEXTVAL);
-- basic autonumber with sequence demo.
-- these insert statements will insert 2 new records into
-- the 'test_bed' table. The user_id field would be assigned
-- the next number from the seq__user_id sequence.
CREATE SEQUENCE seq_user_id;
SELECT seq_user_id.NEXTVAL FROM dual;
INSERT INTO test_bed
(user_id, class_type, room_location)
VALUES
(seq_user_id.NEXTVAL, 'Underwater Basketweaving', 'RM1205');
SELECT *
FROM test_bed;
INSERT INTO test_bed
(user_id, class_type, room_location)
VALUES
(seq_user_id.NEXTVAL, 'Creative Anarchy', 'RM3111');
SELECT * FROM test_bed;
|
