Как я могу использовать переменную пакета в простой инструкции запроса SELECT в Oracle?
Что-то вроде
SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE
Возможно ли это вообще или только при использовании PL/SQL (используйте SELECT в BEGIN/END)?
Вы не можете.
Для публичной переменной пакета, которая будет использоваться в операторе 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.
С уважением,
Роб.
Существует более общий способ, который отлично подходит для меня. Вы создаете функцию с именем входной константы (т.е. Schema.package.constantname), и она возвращает вам постоянное значение. Вы используете немедленный запуск блока PL/SQL путем связывания переменной res (см. Пример).
Функция выглядит следующим образом:
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;
Для этого вам нужна только 1 функция, и вы воспользуетесь преимуществом для использования существующих пакетов .constants.
Примечание. Я только пробовал это в 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 из double;
UNDEFINED_INT
-------------
2147483646
SQL > выберите undefined_string из dual;
UNDEFINED_STRING
--------------------------------------------------------------------------------
?
SQL > выберите undefined_double из double;
UNDEFINED_DOUBLE
----------------
1.798E+308
SQL > выберите to_char (undefined_double, '9.999999999999999EEEE') из dual;
TO_CHAR(UNDEFINED_DOUBL
-----------------------
1.797693134862316E+308
SQL > выберите to_char (undefined_double, '9.99999999999999999EEEE') из dual;
TO_CHAR(UNDEFINED_DOUBLE,
-------------------------
1.79769313486231550E+308
Нет, вы не позволили это сделать. Вам нужно будет предоставить функцию, которая возвращает значение, а затем использовать его в SQL:
SELECT * FROM MyTable WHERE TypeId = MyPackage.FUN_MY_TYPE