在Oracle Object Type构造函数中设置默认值 [英] Setting default value in Oracle Object Type Constructor Function
问题描述
我想在Oracle对象类型中设置默认值,但它要求所有属性都需要在构造函数中传递。
有没有,这样我就只需要在需要默认值的构造函数中传递必需的属性。
请参阅以下详细资料:
SQL> CREATE TYPE TYPE_SUB AS OBJECT(
2 COL1 NUMBER,
3 COL2 VARCHAR2(100)
4)
5最终
6 /
类型创建。
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 /
创建的类型。
SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN是
2构造函数TYPE_MAIN(COL1 NUMBER,COL2 VARCHAR2,COL3 varchar2,COL4 VARCHAR2)由于返回结果是
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结束;
10 END;
11 /
创建类型主体。
SQL> CREATE TABLE TAB_MAIN(
2 PKEY NUMBER,
3 COLTEST VARCHAR2(100),
COLNEW TYPE_MAIN)
5 /
创建表。
SQL> INSERT INTO TAB_MAIN(PKEY)VALUES(1)
2 /
创建了1行。
SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))
2 /
1行创建。
SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))
2 /
1行创建。
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属性传递给默认值,工作。请参阅以下示例。
CREATE TYPE TYPE_SUB AS OBJECT(
2 COL1 NUMBER,
3 COL2 VARCHAR2(100)
4)
5非最终
6 /
创建的类型。
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 /
创建的类型。
SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN是
2 CONSTRUCTOR FUNCTION TYPE_MAIN(COL3 varchar2,COL4 VARCHAR2)由于返回结果是
3 BEGIN
4 SELF。 COL3:= nvl(COL3,'NA');
5 SELF.COL4:= nvl(COL4,NULL);
6 RETURN;
7结束;
8 END;
9 /
创建类型主体。
SQL> CREATE TABLE TAB_MAIN(
2 PKEY NUMBER,
3 COLTEST VARCHAR2(100),
COLNEW TYPE_MAIN)
5 /
创建表。
SQL> INSERT INTO TAB_MAIN(PKEY)VALUES(1)
2 /
创建了1行。
SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))
2 /
1行创建。
SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))
2 /
1行创建。
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)
在第二个代码块中,您没有调用只有两个参数的构造函数。传递一个空参数并不意味着你使用其他构造函数,而是用四个参数调用(默认)构造函数,其中两个恰好为空。你真的在做:
TYPE_MAIN(col1 =>'1',col2 => NULL,col3 => NULL,col4 => NULL)
不是
TYPE_MAIN(col3 =>'1',col4 => NULL)
所以这个工作:
$ b $ pre $ 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默认'不适用',
COL3 varchar2默认'不适用',
COL4 VARCHAR2默认空值)返回自我作为结果)
不是最后
/
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)由于
BEGIN
SELF.COL1:= COL1;
SELF.COL2:= COL2;
SELF.COL3:= COL3;
SELF.COL4:= COL4;
RETURN;
end;
END;
/
然后,当只传递其中一个参数(或者任何时候不是从 col1
>开始顺序传递它们,或者你想要覆盖的前几个,你需要命名它,而不是传递 null code>,因为这只会覆盖默认值。
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')
I want to set default values in Oracle object type, but it requires all attributes needs to be passed in constructor function for that.
Is there anyway, so that I can pass only required attributes in constructor function for which default value is required.
Please see the following details
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)
Now, in above example if I will pass only Col3 and Col4 attribute in constructor function for default value, then it didn't work. Please see the following example.
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)
In your second code block, you aren't calling the constructor that only has two arguments. Passing a null argument doesn't mean you use the other constructor, you're calling the (default) constructor with four arguments, two of which just happen to be intentionally null. You're effectively doing:
TYPE_MAIN(col1 => '1', col2 => NULL, col3 => NULL, col4 => NULL)
not
TYPE_MAIN(col3 => '1', col4 => NULL)
So this works:
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')
To get the defaults to work as I think you intend in your first code block, set them in the constructor parameters, not in the body of the constructor:
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;
/
Then when only passing one of the arguments (or any time you aren't passing them all, or the first few that you want to override in order starting from col1
) you need to name it, rather than passing null
for the others, as that would only override the defaults.
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')
To pass both col3
and col4
you'll still need to name the arguments, otherwise it will assume you're starting from 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')
这篇关于在Oracle Object Type构造函数中设置默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!