Я только что споткнулся об ответ на проблему, с которой я столкнулся, когда переменная PL/SQL не распознавалась функцией, и я надеялся, что кто-нибудь сможет объяснить мне, почему мое решение работает и что происходит «под капотом».
Фон
В рамках проекта оптимизации я пытаюсь собрать показатели для отдельных сценариев SQL в рамках хранимой процедуры. Хранимая процедура, которую я анализирую, имеет параметр даты In-type, который мне нужно определить для запуска каждого отдельного сценария SQL:
CREATE OR REPLACE myStoredProc (DATE_IN DATE, ERROR_OUT OUT VARCHAR2)
IS
BEGIN
--Truncate Temp Tables
--6 Individual SQL Scripts
EXCEPTION
--Error Handling
END;
Чтобы запускать каждый сценарий по отдельности, я решил просто поместить каждое выражение SQL в блок PL/SQL и передать параметр DATE_IN
в качестве переменной:
DECLARE
DATE_IN DATE := TO_DATE('16-JUL-2014','DD-MON-RR');
BEGIN
--Place individual script here
END;
Проблема
Этот подход отлично работал для пары запросов, которые ссылались на эту переменную DATE_IN
, но один запрос со ссылкой на внешнюю функцию, которая принимает DATE_IN
в качестве параметра, начал выдавать ошибку ORA-00904:
DECLARE
DATE_IN DATE := TO_DATE('16-JUL-2014','DD-MON-RR');
BEGIN
insert into temp_table
SELECT table1.field1,
table1.field2,
table2.fieldA,
MyFunction(table1.field1, DATE_IN) --This was the problem line
FROM
table1,
table2
WHERE EXISTS (inner query)
AND table1.keys = table2.keys
AND table2.date <= DATE_IN
END;
Решение
По совету другого разработчика я смог обойти эту ошибку, добавив двоеточие (:) перед переменной DATE_IN
, которую я передал в функцию, чтобы строка проблемы читалась как MyFunction(table1.field1, :DATE_IN)
. Как только я это сделал, моя ошибка исчезла, и я смог выполнить запрос без проблем.
Я был доволен результатом, но другой разработчик не смог объяснить, зачем это нужно, только то, что необходимо было вызывать какие-либо функции или другие хранимые процессы из оператора PL/SQL. Я предполагаю, что это как-то связано с областью действия, но я хотел бы получить лучшее объяснение того, почему это двоеточие было необходимо для того, чтобы функция могла видеть переменную.
Вопросы
Я попытался провести небольшое исследование, просматривая документацию Oracle по параметрам, переменные, привязка/объявление и константы, но в моем исследовании есть только дал мне больше вопросов:
- Прочитав о переменных, я теперь задаюсь вопросом, является ли это правильным термином для того, что я использовал (поскольку я на самом деле не использовал команду VARIABLE и я передаю дату, которая не является допустимым типом данных). Если мой оператор
DATE_IN DATE :=
не является переменной, то что это? - Почему остальные мои ссылки на
DATE_IN
были распознаны компилятором, но передача значения функции вышла за рамки? - Что именно здесь делает двоеточие (:)? Это превращает это в переменную связывания?
Заранее спасибо. Я ценю любое руководство, которое вы можете предоставить!
------------------------------------------------- РЕДАКТИРОВАТЬ------------ --------------------------
Меня попросили предоставить дополнительную информацию. Моя версия БД 11G, 11.2.0.2.0. Запрос, который мне удалось воспроизвести эту ошибку, приведен ниже.
DECLARE
EXTRACT_DT_IN DATE := TO_DATE('16-JUL-2014','DD-MON-RR');
BEGIN
--This begins the pre-optimized query that I'm testing
insert into AELI_COV_TMP_2_OPT
SELECT /*+ ordered use_nl(CM MAMT) INDEX (CM CSMB_CSMB2_UK) INDEX (MAMT (MBAM_CSMB_FK_I) */
CM.CASE_MBR_KEY
,CM.pyrl_no
,MAMT.AMT
,MAMT.FREQ_CD
,MAMT.HOURS
,aeli$cov_pdtodt(CM.CASE_MBR_KEY, EXTRACT_DT_IN)
FROM
CASE_MEMBERS CM
,MEMBER_AMOUNTS MAMT
WHERE EXISTS (select /*+ INDEX(SDEF SLRY_BCAT_FK_I) */
'x'
from SALARY_DEF SDEF
where SDEF.CASE_KEY = CM.CASE_KEY
AND SDEF.TYP_CD = '04'
AND SDEF.SLRY_KEY = MAMT.SLRY_KEY)
AND CM.CASE_MBR_KEY = MAMT.CASE_MBR_KEY
AND MAMT.STAT_CD = '00'
AND (MAMT.xpir_dt is null or MAMT.xpir_dt > EXTRACT_DT_IN)
AND MAMT.eff_dt <= EXTRACT_DT_IN;
--This ends the pre-optimized query that I'm testing
END;
Вот ошибка, с которой я сталкиваюсь при попытке запустить план объяснения для этого оператора. Я могу обойти эту ошибку, если удалю ссылку на строку 13 или добавлю двоеточие (:) к EXTRACT_DT_IN
в этой строке.
----------------------РЕДАКТИРОВАТЬ 2-----
Вот сигнатура функции aeli$.cov_pdtodt. (Я сменил владельца из соображений безопасности).
CREATE OR REPLACE function __owner__.aeli$cov_pdtodt
(CASE_MBR_KEY_IN IN NUMBER, EXTRACT_EFF_DT_IN DATE)
RETURN DATE IS
PDTODT DATE;
:date_in
должна привести к ошибке (или запросу), если вы еще не объявили ее, например. сvariable
. Кажется, работает нормально в 11gR2; какую версию вы используете и какой клиент (и версию)? Не уверен, почему вы думаете, что это проблема области действия, поскольку у вас нет подблоков или повторяющихся имен. - person Alex Poole   schedule 18.07.2014