Total Pageviews

October 1, 2015

10/01/2015 01:37:00 AM
In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column.
You should also consider using Identity columns for this purpose.
In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL clause described in the next section.
CREATE SEQUENCE t1_seq;

CREATE TABLE t1 (
  EMPNO         NUMBER DEFAULT t1_seq.NEXTVAL,
  ENAME VARCHAR2(30)
);

INSERT INTO t1 (ENAME) VALUES ('Sekhar Byna');
INSERT INTO t1 (empno, ename) VALUES (999, 'empno=999 andSekhar Byna);
INSERT INTO t1 (empno, ename) VALUES (NULL, 'empno=NULL and sekhar byna');

SELECT * FROM t1;

        empno ename
---------- ------------------------------
         1 DESCRIPTION only
       999 empno=999 andSekhar Byna
           empno=NULL and sekhar byna

3 rows selected.

SQL>
The fact we can use both the NEXTVAL and CURRVAL pseudocolumns gives us the ability to auto-populate master-detail relationships, as shown below.
CREATE SEQUENCE master_seq;
CREATE SEQUENCE detail_seq;

CREATE TABLE master (
  id          NUMBER DEFAULT master_seq.NEXTVAL,
  description VARCHAR2(30)
);

CREATE TABLE detail (
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)
);

INSERT INTO master (description) VALUES ('Master 1');
INSERT INTO detail (description) VALUES ('Detail 1');
INSERT INTO detail (description) VALUES ('Detail 2');

INSERT INTO master (description) VALUES ('Master 2');
INSERT INTO detail (description) VALUES ('Detail 3');
INSERT INTO detail (description) VALUES ('Detail 4');

SELECT * FROM master;

        ID DESCRIPTION
---------- ------------------------------
         1 Master 1
         2 Master 2

2 rows selected.

SQL>

SELECT * FROM detail;

        ID  MASTER_ID DESCRIPTION
---------- ---------- ------------------------------
         1          1 Detail 1
         2          1 Detail 2
         3          2 Detail 3
         4          2 Detail 4

4 rows selected.

 
Related Posts Plugin for WordPress, Blogger...