Sequences
-- 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);