logo标签列表

如何在SQL SELECT语句中使用包常量？

sqloraclepackage
55

我如何在Oracle中的简单SELECT查询语句中使用包变量？

类似以下方式：

SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE

是否有可能实现，还是只能使用PL/SQL（在BEGIN/END中使用SELECT）？

4个回答
69
你不能直接使用公共包变量在 SQL 语句中，需要编写一个包装函数将其值暴露给外部世界：
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.
11
您应该将函数标记为“deterministic”，否则Oracle在需要“max_number”时会不必要地每次调用它。 - piotrp
16

我有一种更通用的方法，对我来说运作良好。您可以创建一个带有输入常量名称（即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;

像这样，您只需要一个函数，并且可以利用现有的包常量。

2
由于“deterministic”子句的存在，这是一个相当不错的解决方案。 - peter.hrasko.sk
6
好的想法，唯一的缺点是错误（例如由于拼写错误）只会在运行时出现 - 即使您引用了不存在的常量，您的查询也将在没有错误的情况下编译。 - Jeffrey Kemp
谢谢，我认为这个答案比被采纳的更好。 - precise
1
这可能会使数据库面临 SQL 注入的风险。 - durette
8
注意：我只在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> 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
4

不可以这样做。您需要提供一个返回该值的函数，并在SQL中使用它：

SELECT * FROM MyTable WHERE TypeId = MyPackage.FUN_MY_TYPE
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接