注意:我仅在Oracle 11g中尝试过此操作。
我有类似的需求,发现更简单地声明一个函数(不带包)以返回所需的值会更容易。 要将它们放入ddl中以进行导入,请记住用/字符分隔每个函数声明。 例如:
CREATE OR REPLACE FUNCTION UNDEFINED_INT RETURN NUMBER AS BEGIN RETURN 2147483646; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_SHORT RETURN NUMBER AS BEGIN RETURN 32766; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_LONG RETURN NUMBER AS BEGIN RETURN 223372036854775806; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_FLOAT RETURN FLOAT AS BEGIN RETURN .4028233E38; END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_DOUBLE RETURN BINARY_DOUBLE AS BEGIN RETURN to_binary_double('1.7976931348623155E308'); END;
/
CREATE OR REPLACE FUNCTION UNDEFINED_STRING RETURN VARCHAR AS BEGIN RETURN '?'; END;
/
这样,您就可以引用该函数,就好像它是一个常量值一样(例如,您甚至不需要括号)。
例如(请注意to_char方法以显示精度已被保留):SQL>从双选择undefined_int;
UNDEFINED_INT
-------------
2147483646
SQL>从双选择undefined_string;
UNDEFINED_STRING
--------------------------------------------------------------------------------
?
SQL>从双选择undefined_double;
UNDEFINED_DOUBLE
----------------
1.798E+308
SQL>从双重选择to_char(undefined_double,'9.999999999999999EEEE');
TO_CHAR(UNDEFINED_DOUBL
-----------------------
1.797693134862316E+308
SQL>从对偶中选择to_char(undefined_double,'9.99999999999999999EEEE');
TO_CHAR(UNDEFINED_DOUBLE,
-------------------------
1.79769313486231550E+308