Oracle PL/SQL/Sequence/Sequence Value

Automatically including unique sequence numbers during an INSERT.

 
SQL>
SQL> create sequence mySeq start with 1000 increment by 1 cache 100;
Sequence created.
SQL>
SQL> create table myTable (col1 number primary key);
Table created.
SQL>
SQL> insert into myTable values (mySeq.nextval);
1 row created.
SQL> insert into myTable values (mySeq.nextval);
1 row created.
SQL>
SQL> select * from myTable;

      COL1
----------
      1000
      1001
2 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop sequence mySeq;
Sequence dropped.

Get Sequence next value

 

SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2    INCREMENT BY 1
  3    START WITH 1100
  4    MAXVALUE 99999
  5    NOCACHE
  6    NOCYCLE;
Sequence created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select StudentNumSeq.NEXTVAL from DUAL;
                 NEXTVAL
------------------------
                 1100.00
SQL>
SQL> drop sequence StudentNumSeq;
Sequence dropped.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>

If new value is null use the value from a sequence

 
SQL>
SQL>
SQL> create table myTable (id number);
Table created.
SQL>
SQL> create sequence wo_seq;

SQL>
SQL> create or replace trigger trg_id before insert
  2  on myTable
  3  for each row
  4  when (new.id is null)
  5  begin
  6     select wo_seq.nextval into :new.id from dual;
  7  end;
  8  /
Trigger created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Random value based on sequence

 
SQL> create table emp (id number(6) );
Table created.
SQL>
SQL> alter table emp modify id number(12);
Table altered.
SQL>
SQL> create sequence cust_seq cache 1000
  2  start with 100000;
Sequence created.
SQL>
SQL> create or replace procedure gen_emp is
  2   v_new_cid emp.id%type;
  3  begin
  4   insert into emp values (cust_seq.nextval*100000+ round(dbms_random.value(100000,999999)));
  5  end;
  6  /
Procedure created.
SQL>
SQL> begin
  2      for i in 1 .. 10000 loop
  3          gen_emp;
  4      end loop;
  5      commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop sequence cust_seq;
Sequence dropped.
SQL>
SQL>
SQL> drop table emp;
Table dropped.

Sequence current value and next value

 

SQL>
SQL> -- It is important to note that the value of CURRVAL is not populated until 
after NEXTVAL has been used.
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2    INCREMENT BY 1
  3    START WITH 1100
  4    MAXVALUE 99999
  5    NOCACHE
  6    NOCYCLE;
Sequence created.
SQL>
SQL>
SQL> select StudentNumSeq.NEXTVAL from DUAL;
                 NEXTVAL
------------------------
                 1100.00
SQL>
SQL>
SQL> SELECT StudentNumSeq.CURRVAL FROM DUAL;
                 CURRVAL
------------------------
                 1100.00
SQL>
SQL>
SQL> drop sequence StudentNumSeq;
Sequence dropped.
SQL>
SQL>

Sequence max value: 999999999999999999999999999

 
SQL>
SQL>
SQL> -- If you omit the MAXVALUE option, your sequence will automatically default to:
SQL>
SQL>    --  MAXVALUE 999999999999999999999999999
SQL>
SQL> -- So you can simplify your CREATE SEQUENCE command as follows:
SQL>
SQL>     CREATE SEQUENCE mySequence
  2          MINVALUE 1
  3          START WITH 1
  4          INCREMENT BY 1
  5          CACHE 20;
Sequence created.
SQL>
SQL>     select mySequence.nextval from dual;
   NEXTVAL
----------
         1
SQL>
SQL>
SQL>     drop sequence mySequence;
Sequence dropped.
SQL>

Set sequence max value

 

SQL>
SQL> -- A CYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2    INCREMENT BY 50000
  3    START WITH 50000
  4    MAXVALUE   99999
  5    NOCACHE
  6    CYCLE;
Sequence created.
SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;
   NEXTVAL
----------
     50000
SQL> select studentNumSeq.nextVal from dual;
   NEXTVAL
----------
         1
SQL> select studentNumSeq.nextVal from dual;
   NEXTVAL
----------
     50001
SQL>
SQL>
SQL> drop sequence studentNumSeq;
Sequence dropped.
SQL>
SQL>

Use Sequence in a procedure

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE SEQUENCE temp_seq
  2    START WITH 1
  3    INCREMENT BY 1;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert AS
  2  BEGIN
  3      INSERT INTO MyTable (num_col)
  4        VALUES (temp_seq.nextval);
  5      COMMIT;
  6  END TempInsert;
  7  /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert
  2    (p_NextDate IN OUT DATE) AS
  3    v_CurrVal NUMBER;
  4  BEGIN
  5    INSERT INTO MyTable (num_col)
  6      VALUES (temp_seq.nextval);
  7    SELECT temp_seq.currval
  8      INTO v_CurrVal
  9      FROM dual;
 10    IF v_CurrVal > 15 THEN
 11      p_NextDate := NULL;
 12    END IF;
 13    COMMIT;
 14  END TempInsert;
 15  /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
  2    DBMS_JOB.SUBMIT(:v_JobNum, "TempInsert(next_date);", sysdate,
  3                    "sysdate + (1/(24*60*60))");
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> PRINT v_JobNum
  V_JOBNUM
----------
        42
SQL>
SQL>
SQL> drop SEQUENCE temp_seq;
Sequence dropped.
SQL> drop table MyTable;
Table dropped.
SQL>

Use sequence in insert command

 
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> -- A NOCYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE MySeq
  2    INCREMENT BY 1
  3    START WITH 1
  4    MAXVALUE   99999
  5    NOCACHE
  6    NOCYCLE;
Sequence created.
SQL>
SQL>
SQL> insert into emp (empno) values(mySeq.nextVal);
1 row created.
SQL>
SQL> select * from emp;
Hit a key to continue
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
         1
15 rows selected.
SQL> drop sequence mySeq;
Sequence dropped.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>