java oracle 的默认值_在Oracle Object Type Constructor Function中设置默认值

我想在Oracle对象类型中设置默认值，但它需要在构造函数中传递所有属性 .

无论如何，我只能在构造函数中传递必需的属性，该属性需要默认值 .

请参阅以下详细信息

SQL> CREATE TYPE TYPE_SUB AS OBJECT(

2 COL1 NUMBER,

3 COL2 VARCHAR2(100)

4 )

5 NOT FINAL

6 /

Type created.

SQL> CREATE OR REPLACE TYPE TYPE_MAIN

2 UNDER TYPE_SUB

3 (

4 COL3 varchar2(10),

5 COL4 VARCHAR2(10),

6 CONSTRUCTOR FUNCTION TYPE_MAIN(COL1 NUMBER, COL2 VARCHAR2, COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT)

7 NOT FINAL

8 /

Type created.

SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN IS

2 CONSTRUCTOR FUNCTION TYPE_MAIN (COL1 NUMBER, COL2 VARCHAR2, COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT IS

3 BEGIN

4 SELF.COL1 := nvl(COL1,123);

5 SELF.COL2 := nvl(COL2,'NA');

6 SELF.COL3 := nvl(COL3,'NA');

7 SELF.COL4 := nvl(COL4,NULL);

8 RETURN;

9 end;

10 END;

11 /

Type body created.

SQL> CREATE TABLE TAB_MAIN (

2 PKEY NUMBER,

3 COLTEST VARCHAR2(100),

4 COLNEW TYPE_MAIN)

5 /

Table created.

SQL> INSERT INTO TAB_MAIN(PKEY) VALUES(1)

2 /

1 row created.

SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))

2 /

1 row created.

SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))

2 /

1 row created.

SQL> SELECT * FROM TAB_MAIN

2 /

PKEY COLTEST COLNEW(COL1, COL2, COL3, COL4)

---------- ---------- ----------------------------------------

1

1 TESTCOL TYPE_MAIN(1, '2', 'NA', NULL)

1 TESTCOL2 TYPE_MAIN(1, 'NA', 'NA', NULL)

现在，在上面的例子中，如果我只将构造函数中的Col3和Col4属性传递给默认值，那么它就不起作用了 . 请参阅以下示例 .

SQL> CREATE TYPE TYPE_SUB AS OBJECT(

2 COL1 NUMBER,

3 COL2 VARCHAR2(100)

4 )

5 NOT FINAL

6 /

Type created.

SQL> CREATE OR REPLACE TYPE TYPE_MAIN

2 UNDER TYPE_SUB

3 (

4 COL3 varchar2(10),

5 COL4 VARCHAR2(10),

6 CONSTRUCTOR FUNCTION TYPE_MAIN(COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT)

7 NOT FINAL

8 /

Type created.

SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN IS

2 CONSTRUCTOR FUNCTION TYPE_MAIN (COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT IS

3 BEGIN

4 SELF.COL3 := nvl(COL3,'NA');

5 SELF.COL4 := nvl(COL4,NULL);

6 RETURN;

7 end;

8 END;

9 /

Type body created.

SQL> CREATE TABLE TAB_MAIN (

2 PKEY NUMBER,

3 COLTEST VARCHAR2(100),

4 COLNEW TYPE_MAIN)

5 /

Table created.

SQL> INSERT INTO TAB_MAIN(PKEY) VALUES(1)

2 /

1 row created.

SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))

2 /

1 row created.

SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))

2 /

1 row created.

SQL> SELECT * FROM TAB_MAIN

2 /

PKEY COLTEST COLNEW(COL1, COL2, COL3, COL4)

---------- ---------- ----------------------------------------

1

1 TESTCOL TYPE_MAIN(1, '2', NULL, NULL)

1 TESTCOL2 TYPE_MAIN(1, NULL, NULL, NULL)