(Oracle DB) - 13. SEQUENCE

/* Sequence
	#	Returns number by automatically.
	#	For getting unique number by Increment/Decrement Operation
*/

/* CREATE Sequence */
CREATE SEQUENCE my_seq1
	INCREMENT BY 1	-- negative means decrement, default is 1
	START WITH 1
	MINVALUE 1
	MAXVALUE 1000
	NOCYCLE			-- CYCLE means return to start number
	NOCACHE			-- CACHE allocates the sequence value in memory
;

/* INSERT Sequence */
DELETE ex2_8;


	/* currval can not return if sequence is never called */
SELECT my_seq1.CURRVAL	-- CURRVAL means current value of sequence
	FROM dual;
	
INSERT INTO ex2_8
	(col1)
	VALUES
	(my_seq1.NEXTVAL)	-- 1, NEXTVAL returns starting number or next number
;

SELECT my_seq1.CURRVAL	-- 1
	FROM dual;

INSERT INTO ex2_8
	(col1)
	VALUES
	(my_seq1.NEXTVAL)	-- 2
;

SELECT my_seq1.CURRVAL	-- 2
	FROM dual;

SELECT *
	FROM ex2_8;	-- 1, 2 has inserted

INSERT INTO ex2_8
	(col1)
	VALUES
	(my_seq1.NEXTVAL)	-- 3
;

SELECT my_seq1.CURRVAL	-- 3
	FROM dual;

SELECT *
	FROM ex2_8;	-- 3 has inserted

	-- NOTE : NEXTVAL can be operated in SELECT statments.
SELECT my_seq1.NEXTVAL	-- 4
	FROM dual;


SELECT my_seq1.CURRVAL	-- 4
	FROM dual;


/* DROP Sequence */
DROP SEQUENCE my_seq1;