This Online Tech Support page is based on examples to be easier to follow. The Oracle Sequence allowing to get unique integers through an independent object. The Oracle Sequence keeps track of the last number and generates a new one when to call out the NEXTVAL function. The Sequences are returning unique integers per each Oracle Sequence. They are mainly used to fill unique ID columns in Oracle tables or they can be used in applications where unique numbers are needed. To fill ID columns in tables the sequence will be much faster and safer than doing queries over the table rows and looking for the greatest ID value. We do recommend not to use ever the last query option.
The most basic syntax is :
CREATE SEQUENCE <sequence_name> START WITH <number_position> INCREMENT BY <number_amount>;
The first script will create a new sequence named MY_SEQUENCE into your database:
CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1; /
The Oracle Sequence syntax above has keyword “START WITH” and the value will be a starting point to increase the numbers. The number can be set to a higher or lower number depending on what should be the next number. The second keyword named “INCREMENT BY” will increment every next number 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 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. The returned number “2” came from the MY_SEQUENCE object. 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 show how to use the Oracle Sequence in the Oracle insert statement we will need a table. The following script will create a new table named MY_NUMBER.
CREATE TABLE my_number ( ID NUMBER (17)) ;
The next example of the Oracle Insert statement will insert the next value of Sequence MY_SEQUENCE into just created table MY_NUMBER. This insert statement will enter only 1 line and with every next execution there will be one line more and with greater ID value.
INSERT INTO my_number ( ID ) VALUES ( my_sequence.nextval) ;
Let see what is inside the MY_NUMBER table. 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 once the record has been inserted you may need the ID value to store it in some other tables. The most easiest way to get the just inserted ID value is by using keyword RETURNING. We wrote this example in 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>;
You can return any value that was just inserted with the statement but 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 that has been declared above and after 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 by 1 number more and has become number “4“.
To remove Oracle Sequence use the 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 Insert