-- Creating a sequence
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
-- Alter a sequence
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
To list the available sequences and their last assigned values, use:
select sequence_name, last_number from all_sequences where sequence_owner = 'ADMIN';
-- Using sequences
To generate a new sequence use NEXTVAL. Here are some examples:
-- in an INSERT:
INSERT INTO orders (orderno, custno)
VALUES (sequence_name.NEXTVAL, 1032);
-- in the SET clause of an UPDATE statement:
UPDATE orders
SET orderno = sequence_name.NEXTVAL
WHERE orderno = 10112;
-- or the outermost SELECT of a query or subquery, as in:
SELECT sequence_name.NEXTVAL FROM dual;
To use the current sequence use CURRVAL:
INSERT INTO line_items (orderno, partno, quantity)
VALUES (sequence_name.CURRVAL, 20321, 3);
|