Выполнение динамического оператора sql в SYS_REFCURSOR
Можно ли выполнить динамический кусок sql внутри plsql и вернуть результаты в sys_refcursor? Я вложил свою попытку в soo далеко, но dosnt шов, чтобы работать, это ошибка, которую я получаю через свое приложение java.
ORA-01006: привязка переменной не существует ORA-06512: при "LIVEFIS.ERC_REPORT_PK", строка 116 ORA-06512: в строке 1
но это может быть что-то неправильно истолкованное java, все швы для компиляции штрафа soo im not sure.
procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
,pReport out SYS_REFCURSOR) is
begin
declare
lsql varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
||' where c.id = ccf.carer_id (+)'
||' AND cf.id (+) = ccf.cared_for_id';
begin
if pPostcode is not null and pAge <= 0 then
lsql := lsql||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
elsif pPostcode is null and pAge > 0 then
lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge';
elsif pPostcode is not null and pAge > 0 then
lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge'
||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
end if;
execute immediate lsql
into pReport;
end;
end;
Im новый для plsql и даже более новый для динамического sql soo любая помощь/предложения будет значительно оценена.
Еще раз спасибо
Джон
Ответы
Ответ 1
вам нужно будет привязать параметры pAge и pPostcode. В динамическом SQL вы должны префикс их двоеточием (:). Если вы используете EXECUTE IMMEDIATE или OPEN ... FOR, вы привяжете свои параметры по позиции, поэтому я их переименовал: P1 и: P2 в примере:
DECLARE
lsql VARCHAR2(500) := 'SELECT c.id
FROM carer c, cared_for cf, carer_cared_for ccf
WHERE c.id = ccf.carer_id (+)
AND cf.id (+) = ccf.cared_for_id';
BEGIN
IF pPostcode IS NULL THEN
lsql := lsql || ' AND :P1 IS NULL';
ELSE
lsql := lsql || ' AND c.postcode like ''%''|| upper(:P1)||''%''';
IF pPostcode pAge > 0 THEN
lsql := lsql || ' AND :P2 = ROUND((MONTHS_BETWEEN(sysdate,
c.date_of_birth)/12))';
ELSE
lsql := lsql || ' AND nvl(:P2, -1) <= 0';
END IF;
OPEN pReport FOR lsql USING pPostcode, pAge;
END;
Примечание: Число и положение переменных привязки должно быть известно во время компиляции, поэтому я часто использую конструкцию выше (добавление параметра в его положение, даже если оно не используется), Добавление тавтологии (как в AND :P1 IS NULL) к запросу не повлияет на его план объяснения.
Ответ 2
Вы не можете назначить refcursor с помощью немедленного выполнения.
Вам нужно будет построить SQL в строку, а затем использовать open.
sql_str := 'SELECT * FROM...';
open pReport for sql_str;
Ответ 3
Используйте синтаксис OPEN FOR и привяжите переменные.
procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
,pReport out SYS_REFCURSOR)
is
lsql varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
||' where c.id = ccf.carer_id (+)'
||' AND cf.id (+) = ccf.cared_for_id';
begin
if pPostcode is not null and pAge <= 0 then
lsql := lsql||' AND c.postcode like upper(''%''||:1||''%'')';
open pReport for lsql using pPostcode;
elsif pPostcode is null and pAge > 0 then
lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1';
open pReport for lsql using pAge;
elsif pPostcode is not null and pAge > 0 then
lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1'
||' AND c.postcode like upper(''%''||:2||''%'')';
open pReport for lsql using pAge, pPostcode;
end if;
end all_carers_param_dy;
/
Динамический SQL трудно, трудно понять и трудно получить право. Одной из сложных областей является обработка повторения. Это хорошая идея объявить повторяющиеся разделы bolierplate как константы. Также обратите внимание, что мы можем разделить большие строки на несколько строк, не связывая их с '||'. Это уменьшает затраты на обслуживание.
create or replace procedure all_carers_param_dy
(pPostcode in carer.postcode%type
, pAge Number
, pReport out SYS_REFCURSOR)
is
lsql varchar2(500) ;
root_string constant varchar2(500) :='SELECT c.id FROM carer c
, cared_for cf,carer_cared_for ccf
where c.id = ccf.carer_id (+)
and cf.id (+) = ccf.cared_for_id';
pc_string constant varchar2(256) :=
' AND c.postcode like upper(''%''||:pc||''%'')';
age_string constant varchar2(256) :=
' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :age';
begin
if pPostcode is not null and pAge <= 0 then
lsql := root_string || pc_string;
open pReport for lsql using pPostcode;
elsif pPostcode is null and pAge > 0 then
lsql := root_string || age_string;
open pReport for lsql using pAge;
elsif pPostcode is not null and pAge > 0 then
lsql := root_string || age_string
|| pc_string;
open pReport for lsql using pAge, pPostcode;
end if;
end all_carers_param_dy;
/
