SEQUENCE (Oracle)

SEQUENCE (Oracle) is a SQL statement in the Oracle Specific category. Oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. The syntax is CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;. It returns sequence object. A typical example: CREATE SEQUENCE emp_id_seq START WITH 1000 INCREMENT BY 1; INSERT INTO employees (id, name) VALUES (emp_id_seq.NEXTVAL, 'Alice'); -- id = 1000 SELECT emp_id_seq.CURRVAL FROM DUAL; -- Current value: 1000 -- Options: CACHE, CYCLE, MAXVALUE, ORDER, NOCACHE A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c). A close relative is SYSDATE / CURRENT_DATE, which oracle: returns the current date and time of the database server. A close relative is CONNECT BY (Hierarchical), which oracle-specific hierarchical query syntax. Builds tree structures (org charts, bill of materials). More about this category: Oracle-specific features — SEQUENCE, ROWNUM, SYSDATE, CONNECT BY, PL/SQL.