sql - 在 SYS_REFCURSOR 中执行动态 sql 语句

Question

是否可以在 plsql 中执行动态 sql 并将结果返回到 sys_refcursor 中？到目前为止，我已经粘贴了我的尝试，但无法正常工作，这是我通过我的 java 应用程序遇到的错误

ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512: at line 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 新手，因此任何帮助/建议将不胜感激。

再次感谢

乔恩

Answer 1

您必须绑定(bind)参数pAge 和pPostcode。在动态 SQL 中，您可以在它们前面加上冒号 (:)。如果您使用 EXECUTE IMMEDIATE 或 OPEN ... FOR，您将通过位置绑定(bind)您的参数，这就是我在示例中将它们重命名为 :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)不会影响其解释计划。