在SYS_REFCURSOR中执行动态sql语句 [英] Executing a dynamic sql statement into a SYS_REFCURSOR

问题描述

是否可以在plsql中执行动态sql并将结果返回到sys_refcursor中?我已经将尝试粘贴得太远了，但是没有接缝可以正常工作，这是我无法通过我的Java应用程序

is it possible to execute a dynamic piece of sql within plsql and return the results into a sys_refcursor? I have pasted my attempt soo far, but dosnt seam to be working, this is the error im getting throught my java app

ORA-01006:绑定变量没有 存在ORA-06512:位于 "LIVEFIS.ERC_REPORT_PK"，第116行 ORA-06512:在第1行

ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512: at line 1

但是这可能被Java误解了，所有的东西都无法正确编译.

but that could be somthing misconstrued by java, everything seams to compile fine 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;

我对plsql还是陌生的，甚至对动态sql还是较新的，所以任何帮助/建议都将不胜枚举.

Im new to plsql and even newer to dynamic sql soo any help/ suggestions would be greatly apreciated.

再次感谢

乔恩

推荐答案

，您将必须绑定参数pAge和pPostcode.在动态SQL中，您可以在它们前面加上一个冒号(:).如果使用EXECUTE IMMEDIATE或OPEN ... FOR，将通过位置绑定参数，这就是为什么我在示例中将它们重命名为:P1和:P2的原因:

you will have to bind the parameters pAge and pPostcode. In dynamic SQL you would prefix them with a colon (:). If you use EXECUTE IMMEDIATE or OPEN ... FOR, you will bind your parameters via position, this is why I renamed them :P1 and :P2 in the example:

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所示)不会影响其解释计划.

Note: The number and position of bind variables has to be known at compile time, this is why I often use the construct above (adding the parameter to its position even if it is not used). Adding a tautology (as in AND :P1 IS NULL) to a query won't affect its explain plan.

这篇关于在SYS_REFCURSOR中执行动态sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！