SYS_REFCURSOR에 동적 SQL 문 실행

SYS_REFCURSOR에 동적 SQL 문 실행

plsql 내에서 동적 SQL 조각을 실행하고 결과를 sys_refcursor로 반환할 수 있습니까?지금까지 시도를 붙여넣었지만 심이 작동하지 않습니다. Java 앱을 통해 오류가 발생했습니다.

ORA-01006: 바인딩 변수가 없습니다. ORA-06512: "LIVEFIS".ERC_REPORT_PK", 116 ORA-06512: 1행

하지만 그것은 자바에 의해 잘못 해석된 것일 수 있습니다, 모든 것이 잘 컴파일되는 것 같아서 확실하지 않습니다.

 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에 더 새로운 것이기 때문에 어떠한 도움/제안도 매우 감사할 것입니다.

다시한번 감사합니다.

존

매개 변수를 바인딩해야 합니다.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;

참고: bind 변수의 수와 위치는 컴파일 시에 알아야 하므로 위의 구성 요소를 자주 사용합니다(사용하지 않더라도 매개 변수를 위치에 추가).상호작용 추가(예: 에서)AND :P1 IS NULL)를 쿼리에 적용하면 설명 계획에 영향을 주지 않습니다.

즉시 실행을 사용하여 Refursor를 할당할 수 없습니다.

SQL을 문자열로 작성한 다음 open을 사용해야 합니다.

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

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;
/

Dynamic SQL은 이해하기 어렵고 제대로 이해하기 어렵습니다.까다로운 분야 중 하나는 반복을 다루는 것입니다.볼리어 플레이트의 반복 섹션을 상수로 선언하는 것이 좋습니다.또한 여러 줄에 걸쳐 큰 문자열을 분할할 수 있습니다.'||'이를 통해 유지 관리 오버헤드를 줄일 수 있습니다.

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; 
/ 

네, 가능합니다.다음과 같이 수행:

v_sql := 'BEGIN OPEN :1 FOR :2 USING ';
v_bindvars := pPostcode ||', '||pAge; --this part you can create dynamically base on your if's
v_sql := v_sql||v_bindvars||' ; END;';
v_select := 'select yourdata from dual where 1 = :bind_first_var and 2 = :bind_second_var';

execute immediate v_sql using pReport, v_select; 

언급URL : https://stackoverflow.com/questions/2230428/executing-a-dynamic-sql-statement-into-a-sys-refcursor