Oracle PL/SQL Tutorial/Sequences/Create Sequence

Create a sequence

SQL>
SQL>
SQL> CREATE SEQUENCE myTableIDSeq;
Sequence created.
SQL>
SQL>
SQL> drop sequence myTableIDSeq;
Sequence dropped.
SQL>

Create a sequence starting with 1000

SQL>
SQL> create sequence s_emp_id
  2  start with 1000;
Sequence created.
SQL>
SQL>
SQL> drop sequence s_emp_id;
Sequence dropped.
SQL>

Create a sequence starting with "100000000 order"

SQL>
SQL> create sequence EXTERNAL_ID_SEQ
  2  start with 100000000 order;
Sequence created.
SQL>
SQL> drop   sequence EXTERNAL_ID_SEQ;
Sequence dropped.
SQL>

create sequence deptno_seq start with 50 increment by 10

SQL>
SQL>
SQL> create sequence deptno_seq start with 50 increment by 10;
Sequence created.
SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval
  2  from   dual;
   NEXTVAL    CURRVAL
---------- ----------
        50         50
SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.
SQL>
SQL>

create sequence emps_seq start with 501 increment by 10

SQL>
SQL> create sequence emps_seq
  2     start with 501
  3     increment by 10;
Sequence created.
SQL>
SQL> drop sequence emps_seq;
Sequence dropped.
SQL>
SQL>

CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;

SQL>
SQL>
SQL>
SQL> CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;
Sequence created.

CREATE SEQUENCE test_seq;

Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         1
SQL> SELECT test_seq.currval FROM dual;
   CURRVAL
----------
         1
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         2
SQL>
SQL> ALTER SEQUENCE test_seq
  2  INCREMENT BY 2;
Sequence altered.
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         4
SQL> SELECT test_seq.currval FROM dual;
   CURRVAL
----------
         4
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         6
SQL>
SQL>
SQL> drop sequence my_seq;
SQL>

Create the simplest sequence

SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL>
SQL> drop sequence test_seq;
Sequence dropped.
SQL>

Creating a sequence and then get the next value

SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         1
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         2
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         3
SQL>
SQL> DROP SEQUENCE test_seq;
Sequence dropped.
SQL>
SQL>

Sequences

A sequence is a database item that generates a sequence of integers.

You typically use the integers generated by a sequence to populate a numeric primary key column.

You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:

CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];

Test sequence step

SQL>
SQL> CREATE SEQUENCE test_seq
  2  START WITH 10 INCREMENT BY 5
  3  MINVALUE 10 MAXVALUE 20
  4  CYCLE CACHE 2 ORDER;
Sequence created.
SQL>
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
        10
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
        15
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
        20
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
        10
SQL>
SQL>
SQL> drop sequence test_seq
  2  /
Sequence dropped.
SQL>
SQL>

Using a sequence to populate a table"s column

SQL>
SQL>
SQL> CREATE TABLE test (
  2       record_id   NUMBER(18,0),
  3       record_text VARCHAR2(10)
  4  );
Table created.
SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         1
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         2
SQL> SELECT test_seq.nextval FROM DUAL;
   NEXTVAL
----------
         3
SQL>
SQL>
SQL> INSERT INTO test VALUES (
  2       test_seq.nextval,
  3       "Record A"
  4  );
1 row created.
SQL>
SQL> INSERT INTO test VALUES (
  2       test_seq.nextval,
  3       "Record B"
  4  );
1 row created.
SQL>
SQL> SELECT * FROM test;
 RECORD_ID RECORD_TEX
---------- ----------
         4 Record A
         5 Record B
SQL>
SQL>
SQL> DROP SEQUENCE test_seq;
Sequence dropped.
SQL>
SQL> drop table test;
Table dropped.
SQL>