|
In Oracle 12C, there're two ways to provide AutoNumber or Identity functionality of other database engines like SQL Server.
(1) Identity Columns
The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.
GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
Example:
CREATE TABLE test(
id NUMBER GENERATED ALWAYS AS IDENTITY,
text VARCHAR2(100)
);
INSERT INTO TEST( TEXT) VALUES ('Test Line#1');
INSERT INTO TEST( TEXT) VALUES ('Test Line#2');
SELECT * FROM TEST;
(2) DEFAULT Values Using Sequences
In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column.
CREATE SEQUENCE t1_seq;
CREATE TABLE t1 (
id NUMBER DEFAULT t1_seq.NEXTVAL,
description VARCHAR2(30)
);
INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
Ref:
http://www.oracle-base.com/artic ... in-oracle-12cr1.php
http://www.oracle-base.com/artic ... ancements-12cr1.php
|
|