Oracle PL/SQL/Sequence/Create Sequence

Create a SEQUENCE and retrieve the value

 
SQL>
SQL> CREATE SEQUENCE supplier_seq
  2      MINVALUE 1
  3      MAXVALUE 999999999999999999999999999
  4      START WITH 1
  5      INCREMENT BY 1
  6      CACHE 20;
Sequence created.
SQL>
SQL> select supplier_seq.nextval from dual;
   NEXTVAL
----------
         1
SQL> select supplier_seq.nextval from dual;
   NEXTVAL
----------
         2
SQL> select supplier_seq.nextval from dual;
   NEXTVAL
----------
         3
SQL> select supplier_seq.nextval from dual;
   NEXTVAL
----------
         4
SQL>
SQL> drop sequence supplier_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.

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.

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.