如何在 SQL SELECT 语句中使用包常量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5178830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a package constant in SQL SELECT statement?
提问by blerontin
How can I use a package variable in a simple SELECT query statement in Oracle?
如何在 Oracle 的简单 SELECT 查询语句中使用包变量？
Something like
就像是
SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE
Is it possible at all or only when using PL/SQL (use SELECT within BEGIN/END)?
是否可以使用 PL/SQL（在 BEGIN/END 中使用 SELECT）？
回答by Rob van Wijk
You can't.
你不能。
For a public package variable to be used in a SQL statement, you have to write a wrapper function to expose the value to the outside world:
对于要在 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
Create a wrapper function:
创建一个包装函数：
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.
And now it works:
现在它起作用了：
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.
回答by Bj?rn
There is a more generic way which works fine for me. You create a function with input constant name (i.e. schema.package.constantname) and it returns you the constant value. You make use of executing immediate a PL/SQL block by binding res variable (see example).
有一种更通用的方法对我来说很好用。您使用输入常量名称（即 schema.package.constantname）创建一个函数，它会返回常量值。您可以通过绑定 res 变量来立即执行 PL/SQL 块（参见示例）。
Function looks like this:
函数看起来像这样：
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;
/
You can then use the constant of any package in any SQL, i.e. like
然后您可以在任何 SQL 中使用任何包的常量，即
select GETCONSTANTVALUE('PKGGLOBALCONSTANTS.constantname') from dual;
Like this you need only 1 function and you take the advantage to use existing packages.constants.
像这样你只需要 1 个函数，你就可以利用现有的 packages.constants。
回答by Nathaniel Mills
Note: I only tried this in Oracle 11g.
注意：我只在 Oracle 11g 中尝试过这个。
I had a similar need and found it easier to simply declare a function (without the package) to return the desired value. To put these in ddl for import, remember to separate each function declaration with the / character. For example:
我有类似的需求，发现简单地声明一个函数（没有包）来返回所需的值更容易。要将它们放入 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;
/
This allows you to reference the function as though it was a constant value (e.g., you don't even need the parentheses).
这允许您像引用一个常量值一样引用该函数（例如，您甚至不需要括号）。
For example (note the to_char methods to show the precision has been preserved): SQL> select undefined_int from dual;
例如（注意显示精度的 to_char 方法已被保留）： SQL> select undefined_int from dual;
UNDEFINED_INT
-------------
2147483646
SQL> select undefined_string from dual;
SQL> select undefined_string from dual;
UNDEFINED_STRING
--------------------------------------------------------------------------------
?
SQL> select undefined_double from dual;
SQL> select undefined_double from dual;
UNDEFINED_DOUBLE
----------------
1.798E+308
SQL> select to_char(undefined_double,'9.999999999999999EEEE') from dual;
SQL> select to_char(undefined_double,'9.999999999999999EEEE') from dual;
TO_CHAR(UNDEFINED_DOUBL
-----------------------
1.797693134862316E+308
SQL> select to_char(undefined_double,'9.99999999999999999EEEE') from dual;
SQL> select to_char(undefined_double,'9.999999999999999999EEEE') from dual;
TO_CHAR(UNDEFINED_DOUBLE,
-------------------------
1.79769313486231550E+308
回答by Tony Andrews
No, you aren'e allowed to do that. You would need to provide a function that returns the value and then use that in the SQL:
不，你不能这样做。您需要提供一个返回值的函数，然后在 SQL 中使用它：
SELECT * FROM MyTable WHERE TypeId = MyPackage.FUN_MY_TYPE
