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.
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