oracle - 在 Oracle 对象类型构造函数中设置默认值

Question

我想在 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)

Answer 1

在您的第二个代码块中，您没有调用只有两个参数的构造函数。传递 null 参数并不意味着您使用另一个构造函数，您正在调用带有四个参数的(默认)构造函数，其中两个恰好是故意为 null。你正在有效地做:

TYPE_MAIN(col1 => '1', col2 => NULL, col3 => NULL, col4 => NULL)

不是

TYPE_MAIN(col3 => '1', col4 => NULL)

所以这是可行的:

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('3','4'))
/

...

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2',NULL,NULL)           
         1 TESTCOL2        TYPE_MAIN(1,NULL,NULL,NULL)          
         1 TESTCOL2        TYPE_MAIN(NULL,NULL,'3','4')         

要让默认值按照我认为您在第一个代码块中的意图工作，请在构造函数参数中设置它们，而不是在构造函数的主体中:

CREATE OR REPLACE TYPE TYPE_MAIN
UNDER TYPE_SUB
(
COL3 varchar2(10),
COL4 VARCHAR2(10),
CONSTRUCTOR FUNCTION TYPE_MAIN(COL1 number default 123,
  COL2 VARCHAR2 default 'NA',
  COL3 varchar2 default 'NA',
  COL4 VARCHAR2 default null) RETURN SELF AS RESULT)
NOT FINAL
/

CREATE OR REPLACE TYPE BODY TYPE_MAIN  IS
CONSTRUCTOR FUNCTION TYPE_MAIN (COL1 number default 123,
  COL2 VARCHAR2 default 'NA',
  COL3 varchar2 default 'NA',
  COL4 VARCHAR2 default null) RETURN SELF AS RESULT IS
BEGIN
 SELF.COL1 := COL1;
 SELF.COL2 := COL2;
 SELF.COL3 := COL3;
 SELF.COL4 := COL4;
RETURN;
end;
END;
/

然后当只传递一个参数时(或者任何时候你没有传递所有参数，或者你想要覆盖的前几个参数从 col1 开始)你需要命名它，而不是为其他人传递 null，因为那样只会覆盖默认值。

INSERT INTO TAB_MAIN(PKEY) VALUES(1)
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL',TYPE_MAIN(1,'2'))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL2',TYPE_MAIN(1))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL3',TYPE_MAIN(col3 => '3'))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL4',TYPE_MAIN(col4 => '4'))
/

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2','NA',NULL)           
         1 TESTCOL2        TYPE_MAIN(1,'NA','NA',NULL)          
         1 TESTCOL3        TYPE_MAIN(123,'NA','3',NULL)         
         1 TESTCOL4        TYPE_MAIN(123,'NA','NA','4')         

要同时传递 col3 和 col4 你仍然需要命名参数，否则它会假设你从 col1 开始:

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL5',TYPE_MAIN(col3 => '3', col4 => '4'))
/

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2','NA',NULL)           
         1 TESTCOL2        TYPE_MAIN(1,'NA','NA',NULL)          
         1 TESTCOL3        TYPE_MAIN(123,'NA','3',NULL)         
         1 TESTCOL4        TYPE_MAIN(123,'NA','NA','4')         
         1 TESTCOL5        TYPE_MAIN(123,'NA','3','4')