Oracle PL/SQL Tutorial/Sequences/Create Sequence

Create a sequence

   <source lang="sql">

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

Create a sequence starting with 1000

   <source lang="sql">

SQL> SQL> create sequence s_emp_id

 2  start with 1000;

Sequence created. SQL> SQL> SQL> drop sequence s_emp_id; Sequence dropped. SQL></source>

Create a sequence starting with "100000000 order"

   <source lang="sql">

SQL> SQL> create sequence EXTERNAL_ID_SEQ

 2  start with 100000000 order;

Sequence created. SQL> SQL> drop sequence EXTERNAL_ID_SEQ; Sequence dropped. SQL></source>

create sequence deptno_seq start with 50 increment by 10

   <source lang="sql">

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></source>

create sequence emps_seq start with 501 increment by 10

   <source lang="sql">

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></source>

CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;

   <source lang="sql">

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

CREATE SEQUENCE test_seq;

   <source lang="sql">

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></source>

Create the simplest sequence

   <source lang="sql">

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

Creating a sequence and then get the next value

   <source lang="sql">

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></source>

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:

   <source lang="sql">

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 } ];</source>

Test sequence step

   <source lang="sql">

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></source>

Using a sequence to populate a table"s column

   <source lang="sql">

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></source>