How To Use A SEQUENCE In Oracle

This help is based on examples so it would be easier to understand. Object Oracle Sequence allows to get unique integers. The Oracle Sequence keeps track of the last generated number and returns a new one when to use function NEXTVAL. The Sequence objects are for to return unique integers per Oracle Sequence and they can be used to fill the unique ID column in Oracle table. Using Oracle Sequence you don’t have to do queries over the table rows to look for the last ID value. Now with sequence it will be much easier and faster to get the ID values.

The basic Oracle Sequence syntax is :

CREATE SEQUENCE <sequence_name>
 START WITH     <number_position>
 INCREMENT BY   <number_amount>;

The first script will create a new Oracle sequence named MY_SEQUENCE.

CREATE SEQUENCE my_sequence
 START WITH     1
 INCREMENT BY   1;
/

The last Oracle Sequence syntax above has keyword “START WITH” and the value will be a starting point to increase the integers. The integer can be set to a higher or lower number depending on what should be the next value. The second keyword named “INCREMENT BY” will increment every next value by the amount set by the parameter. The “INCREMENT BY” value is by default “1“. When you’ll leave the “INCREMENT BY” value to “1” then the Oracle Sequence will return the following values: 2,3,4,5,.. and when it has to been set the to “2” then the Sequence will return 2,4,6,8,10,..

The first example shows how to get the next Sequence value and you can do it using a simple SQL query with Oracle Dual table. The number “2” came from the MY_SEQUENCE object and If you’ll run the query one more time the next value will be “3” and so on.

SELECT my_sequence.nextval
 FROM dual;

To demonstrate how to use the Sequence in the Oracle insert statement we will need an Oracle table. The following script will create a new table named MY_NUMBER.

CREATE TABLE my_number
    ( ID NUMBER (17)) ;

The following example with the Oracle Insert statement will insert the next value of the MY_SEQUENCE into just created table MY_NUMBER. This insert statement will enter only 1 line and with every next execution there will be an extra line with greater ID value.

INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval) ;

Lets take a look what is inside the MY_NUMBER table now. The value number “3” means the MY_SEQUENCE objects has increased its value by one more number.

SELECT *
   FROM my_number;

To use Oracle Sequence in the insert statement and after the record has been inserted you may need the new ID value to store it in other tables. The most easiest way to get the just inserted ID value is by using keyword RETURNING. We wrote this example in a PL/SQL anonymous block but you can use the RETURNING keyword with the bind variables too. The Oracle Insert with the Returning keyword is following.

INSERT INTO <table_name>
    ( <table_columns> )
    VALUES
    ( <my_values>)
      RETURNING <column_names> into <variables>;

The RETURNING keyword can return any value that was just inserted with your insert statement and the next example returns ID value that has been generated by Oracle Sequence during the insert.

DECLARE
  v_id number;
BEGIN
 INSERT INTO my_number
    ( ID )
    VALUES
    ( my_sequence.nextval)
      RETURNING ID into v_id ;

 DBMS_OUTPUT.PUT_LINE('value v_id is '||v_id);

END;
/

The ID value is stored into V_ID variable has been declared above and later the insert procedure DBMS_OUTPUT.PUT_LINE sends text “value v_id is x” to output. Take a look at the output above and take a note the MY_SEQUENCE value has increased more by 1 number and has become number “4“.

To remove Oracle Sequence use a DROP SEQUENCE command as the following syntax:

DROP SEQUENCE <sequence_name>;

The next script will drop just created MY_SEQUENCE sequence.

DROP SEQUENCE my_sequence;
/

Also the Oracle Sequence can be used in Oracle Triggers, procedures or functions to insert the unique ID value into the Oracle tables.

See Also:
Oracle Select Oracle Rownum Oracle Home