Выполнение динамического оператора 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; 
/