Oracle PL/SQL Tutorial/Sequences/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 next value from sequence

SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));
Table created.
SQL>
SQL> create sequence s_emp_id
  2  start with 1000;
Sequence created.
SQL>
SQL> insert into emp (emp_id, lastname, firstname)
  2  values   (s_emp_id.nextval, "Einstein", "Albert");
1 row created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop sequence s_emp_id;
Sequence dropped.

If id is null, use the value from sequence

SQL>
SQL> create table myTable(
  2      id           number primary key,
  3      blob_content blob
  4  )
  5  /
Table created.
SQL>
SQL> create sequence myTable_seq
  2  /
Sequence created.
SQL>
SQL> create or replace trigger biu_myTable
  2      before insert or update on myTable
  3      for each row
  4  begin
  5      if :new.id is null then
  6          select myTable_seq.nextval into :new.id from dual;
  7      end if;
  8  end;
  9  /
Trigger created.
SQL> drop table myTable;
Table dropped.
SQL> drop sequence myTable_seq;
Sequence dropped.

Once initialized, you can get the current value from the sequence using currval.

SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         1
SQL>
SQL>
SQL> SELECT test_seq.currval FROM dual;
   CURRVAL
----------
         1
SQL>
SQL>
SQL>
SQL> drop sequence test_seq
  2  /
Sequence dropped.
SQL>

Query current sequence value

SQL>
SQL> create sequence deptno_seq start with 50 increment by 10;
Sequence created.
SQL>
SQL> select deptno_seq.currval
  2  from   dual;
select deptno_seq.currval
       *
ERROR at line 1:
ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session

SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.
SQL>
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.

select deptno_seq.currval, deptno_seq.nextval

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

Set start value, mini/max value and cache

SQL>
SQL> CREATE SEQUENCE test_seq
  2  START WITH 10 INCREMENT BY -1
  3  MINVALUE 1 MAXVALUE 10
  4  CYCLE CACHE 5;
Sequence created.
SQL>
SQL> select * from user_sequences where sequence_name="TEST_SEQ";
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ                                1         10           -1 Y N          5          10
SQL>
SQL>
SQL>
SQL> drop sequence test_seq
  2  /
Sequence dropped.

Use dual table to check sequence

SQL>
SQL> create sequence deptno_seq
  2  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> select deptno_seq.currval
  2  from   dual;
   CURRVAL
----------
        50
SQL>
SQL> select deptno_seq.currval, deptno_seq.nextval
  2  from   dual;
   CURRVAL    NEXTVAL
---------- ----------
        60         60
SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.

When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.

SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM dual;
   NEXTVAL
----------
         1
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         2          2
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         3          3
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         4          4
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         5          5
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         6          6
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
   NEXTVAL    CURRVAL
---------- ----------
         7          7
SQL>
SQL> drop sequence test_seq
  2  /
Sequence dropped.