我如何在Oracle中的简单SELECT查询语句中使用包变量?
类似以下方式:
SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE
是否有可能实现,还是只能使用PL/SQL(在BEGIN/END中使用SELECT)?
SQL> create package my_constants_pkg
2 as
3 max_number constant number(2) := 42;
4 end my_constants_pkg;
5 /
Package created.
SQL> with t as
2 ( select 10 x from dual union all
3 select 50 from dual
4 )
5 select x
6 from t
7 where x < my_constants_pkg.max_number
8 /
where x < my_constants_pkg.max_number
*
ERROR at line 7:
ORA-06553: PLS-221: 'MAX_NUMBER' is not a procedure or is undefined
SQL> create or replace package my_constants_pkg
2 as
3 function max_number return number;
4 end my_constants_pkg;
5 /
Package created.
SQL> create package body my_constants_pkg
2 as
3 cn_max_number constant number(2) := 42
4 ;
5 function max_number return number
6 is
7 begin
8 return cn_max_number;
9 end max_number
10 ;
11 end my_constants_pkg;
12 /
Package body created.
现在它可以工作了:
SQL> with t as
2 ( select 10 x from dual union all
3 select 50 from dual
4 )
5 select x
6 from t
7 where x < my_constants_pkg.max_number()
8 /
X
----------
10
1 row selected.
我有一种更通用的方法,对我来说运作良好。您可以创建一个带有输入常量名称(即schema.package.constantname)的函数,并返回常量值。您可以利用绑定res变量执行 PL/SQL 块的方法(请参见示例)。
函数如下:
CREATE OR REPLACE FUNCTION GETCONSTANTVALUE (i_constant IN VARCHAR2) RETURN NUMBER deterministic AS
res number;
BEGIN
execute immediate 'begin :res := '||i_constant||'; end;' using out res;
RETURN res;
END;
/
您可以在任何SQL中使用任何软件包的常量,例如:
select GETCONSTANTVALUE('PKGGLOBALCONSTANTS.constantname') from dual;
像这样,您只需要一个函数,并且可以利用现有的包常量。
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> select undefined_int from dual;
UNDEFINED_INT
-------------
2147483646
SQL> select undefined_string from dual;
UNDEFINED_STRING
--------------------------------------------------------------------------------
?
SQL> select undefined_double from dual;
UNDEFINED_DOUBLE
----------------
1.798E+308
SQL> 从dual表中选择to_char(undefined_double,'9.999999999999999EEEE');
(意思是将undefined_double转换为科学计数法字符串)TO_CHAR(UNDEFINED_DOUBL
-----------------------
1.797693134862316E+308
SQL> 从dual中选择to_char(undefined_double,'9.99999999999999999EEEE');
这条SQL语句意思是将undefined_double转换为科学计数法表示的字符串。
TO_CHAR(UNDEFINED_DOUBLE,
-------------------------
1.79769313486231550E+308
不可以这样做。您需要提供一个返回该值的函数,并在SQL中使用它:
SELECT * FROM MyTable WHERE TypeId = MyPackage.FUN_MY_TYPE