Oracle PL/SQL/Sequence/Sequence Increment

A decrement sequence

   <source lang="sql">

SQL> SQL> -- A decrement sequence SQL> SQL> SQL> CREATE SEQUENCE StudentNumSeq

 2    INCREMENT BY -1
 3    START WITH 50000
 4    MAXVALUE   99999
 5    MINVALUE   0
 6    NOCACHE
 7    NOCYCLE;

Sequence created. SQL> SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    50000

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    49999

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    49998

SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL

---

    49997

SQL> SQL> SQL> drop sequence studentNumSeq; Sequence dropped. SQL> SQL> SQL>

 </source>
   
  

Get next value from sequence

   <source lang="sql">

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.

 </source>