Sequences

Sequences can be used as automated counters and can be accessed using the CURRVAL and NEXTVAL pseudocolumns.

• sequence.CURRVAL
• sequence.NEXTVAL

INSERT INTO subject(subject_id, parent_id, name)
VALUES (subject_seq.NEXTVAL
	,(SELECT subject_id FROM subject WHERE name = 'Fiction')
	,'Subject name');

SELECT subject_seq.CURRVAL FROM DUAL;

Connecting to a new session in SQL*Plus can demonstrate this.

SELECT author_seq.CURRVAL FROM DUAL;
       *
ERROR at line 1:
ORA-08002: sequence AUTHOR_SEQ.CURRVAL is not yet defined in this session


SELECT author_seq.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        21

SELECT author_seq.CURRVAL FROM DUAL;

   CURRVAL
----------
        21

Sequence Syntax

CREATE SEQUENCE [schema.]sequence
	[ START WITH n ] [ INCREMENT BY n ]
	[ MINVALUE { n } | NOMINVALUE ] [ MAXVALUE { n } | NOMAXVALUE ]
	[ [ NO ]CACHE ] [ [ NO ]CYCLE ] [ [ NO ]ORDER ];

ALTER SEQUENCE [schema.]sequence
	[ INCREMENT BY n ]
	[ MINVALUE { n } | NOMINVALUE ] [ MAXVALUE { n } | NOMAXVALUE ]
	[ [ NO ]CACHE ] [ [ NO ]CYCLE ] [ [ NO ]ORDER ];

DROP SEQUENCE [ schema.]sequence;

Synonyms

A synonym is a pointer to a table or view, perhaps in another schema or across a database link into another database.

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
	[ schema.]synonym
	FOR [ schema.]object[@ database link ]; 

Views

A view is an overlay of one or more tables, views or synonyms where a SQL statement making up the view is executed whenever the view is accessed. Commonly views are used for security purposes where different parts of tables can be accessed by different users. Views can be also used to change data in underlying tables, with obvious severe restrictions.

CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW [schema.]view
	(alias constraint-clause [, alias constraint-clause ... ] )
	AS subquery
	[ WITH { READ ONLY
		| CHECK OPTION [ CONSTRAINT constraint ] } ];

ALTER VIEW [schema.]view 
{
	  ADD table constraint
	| MODIFY CONSTRAINT constraint { [ NO]RELY }
	| DROP { CONSTRAINT constraint | PRIMARY KEY
		| UNIQUE (column [, column ...]) }
	| COMPILE
};

DROP VIEW view;