Выполнение динамического sql оператора в SYS_REFCURSOR

Question

Выполнение динамического sql оператора в SYS_REFCURSOR

действительно ли возможно выполнить динамическую часть sql в plsql и возвратить результаты в sys_refcursor? Я вставил свою попытку до сих пор, но dosnt шов для работы это - ошибка, которую я получаю через свое приложение Java

РТЫ 01006: свяжите переменную, не существует РТЫ 06512: в "LIVEFIS.ERC_REPORT_PK", строка 116 РТОВ 06512: в строке 1

но это могло быть чем-то неверно истолкованным Java, все швы для компиляции прекрасный, таким образом, я не уверен.

 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;

Я плохо знаком с plsql и еще более плохо знаком с динамическим sql, таким образом, любая справка / предложения значительно ценилась бы.

Еще раз спасибо

Jon

11

sql oracle plsql

задан user250643 9 February 2010 в 16:01

3 ответа

Другие вопросы по тегам:

sql oracle plsql

Похожие вопросы:

score 8 · Answer 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 ) не повлияет на его план объяснения.

score 0 · Answer 2

Используйте синтаксис 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; 
/

score 5 · Answer 3

Вы не можете назначить рефкурсор с помощью немедленного выполнения.

Вам нужно будет построить SQL в строку, а затем использовать open.

sql_str := 'SELECT * FROM...';
open pReport for sql_str;