Oracle SQL - Table Sequences

Introduction

Sequences can be created, changed, and dropped with the following three SQL commands:

  • CREATE SEQUENCE
  • ALTER SEQUENCE
  • DROP SEQUENCE

A sequence definition may consist of a start value, increment value, minimum value, and maximum value.

You can specify whether the sequence generator should stop when reaching a boundary value, or CYCLE the sequence numbers within the minimum/maximum range.

All sequence attributes are optional and they all have default values.

Each sequence has two pseudo columns: NEXTVAL and CURRVAL.

The following code shows how you can create and use a sequence DEPTNO_SEQ to generate department numbers, using the DUAL table.

Normally you would use sequence values in INSERT statements.

Demo

SQL>
SQL> create sequence deptno_seq
  2  start with 50 increment by 10;-- w ww . j a v a2s  . c om

SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval from dual;

  NEXTVAL |   CURRVAL
--------- | ---------
 00450.00 |  00450.00

SQL> select deptno_seq.currval from dual;

  CURRVAL
---------
 00450.00

SQL> select deptno_seq.currval, deptno_seq.nextval from dual;

  CURRVAL |   NEXTVAL
--------- | ---------
 00460.00 |  00460.00

SQL>

You can use CURRVAL multiple times in different SQL statements.

Once you have selected NEXTVAL in an earlier statement. Then use the same value (CURRVAL) several times to insert multiple line items for that order.