Thursday 21 August 2014

How To Add Auto Increment In Oracle- IDENTITY column in Oracle 12c

Lets create employee table for this purpose.
SQL> CREATE TABLE EMP01
(
EMPID NUMBER,
NAME VARCHAR2(50),
DEPT VARCHAR2(20)
);

Next steps is to create oracle sequence to generated the id values.
SQL> CREATE SEQUENCE EMPID_SEQUENCE
START WITH 1
INCREMENT BY 1;

Next Step is to create Trigger to assign the values from sequence to EMPID column.

CREATE OR REPLACE TRIGGER EMPID_TRIGGER
BEFORE INSERT ON EMP01
FOR EACH ROW
BEGIN
SELECT EMPID_SEQUENCE.nextval INTO :NEW. EMPID  FROM dual;
END;
/
Now we will try insertng few values:

SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('RON',’ABC’);
1 row created.
SQL> INSERT INTO EMP01 (NAME, DEPT) VALUES ('VICTORIA',’XYZ’);
SQL> SELECT * FROM EMP01;
EMPID NAME DEPT
---------- ------------------------------
1 RON ABC
2 VICTORIA XYZ


You can find that the EMPID getting incremented by 1.
Now there is a new feature available in on Oracle 12c version:IDENTITY column using which we can implement the same auto increment feature.

Identity columns can be specified in oracle table which will generate value by itself. Oracle implicitly creates a sequence and assigns it to the identity column for each insert.

Syntax: 
GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

CREATE TABLE EMP01
(
EMPID NUMBER GENERATED BY DEFAULT AS IDENTITY,
NAME VARCHAR2(50),
);

A sequence is created automatically by Oracle when the table is created. default will use identity if the column is not referenced in the insert statement, if it’s referenced then the value specified will be used.

SQL> insert into EMP01 (NAME) values ('ROY');
SQL> select * from EMP01;

     EMPID NAME
------- ----------

       1 ROY


>>>>>>>   Mail @dwhlaureate@gmail.com for any queries    <<<<<<<<<

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis