oracle10g 存储过程中，如何将多条动态sql的执行结果放入一个结果集，请高手指点！！

Question

oracle10g 存储过程中，如何将多条动态sql的执行结果放入一个cusror,改cursor为返回参数
代码如下：
create or replace procedure complaint_Detail(start_date Date,end_date Date,PList OUT pkg_workflow.W_cursor)
is
   sqlStr varchar2(3000);
   cursor cur is 
          select PROPDATAINDEX from CRM_PRODUCT_FORM_PROPERTIES property,CRM_PRODUCT_INFO info,crm_workflows flow 
          where property.PRODUCTID = info.productid 
          and info.workflowid = flow.workflowid
          and flow.workflowsubmittime>=start_date
          and flow.workflowhandlertime<=end_date;
begin
     for cur_result in cur loop
     begin
       sqlStr := null;
       sqlStr:='select flow.WORKFLOWID,';
       sqlStr:='first.productname,';
       sqlStr:='third.productname,';
       sqlStr:='property.propcnname';
       sqlStr:='||'':''||';
       sqlStr:='info.DATAVALUE'; 
       sqlStr:= cur_result.PROPDATAINDEX;
       sqlStr:=',flow.WORKFLOWHANDLER,flow.WORKFLOWHANDLERTIME,';
       sqlStr:='flow.HANDLERPROGRESS,flow.WORKFLOWINITIATOR,';
       sqlStr:=' flow.WORKFLOWHANDLER,flow.WORKFLOWSUBMITTIME,flow.ISTIMEOUT';
       sqlStr:=' from crm_product_info info,crm_workflows flow,CRM_PRODUCT_FORM_PROPERTIES property,';
       sqlStr:=' (select productname from crm_product_types,crm_workflows flow where productid = flow.PRODFIRID) first,';
       sqlStr:=' (select productname from crm_product_types,crm_workflows flow where productid = flow.PRODTHIID) third';
       sqlStr:=' where flow.workflowid = info.workflowid ';
       sqlStr:=' and property.PRODUCTID = info.productid ';
       sqlStr:=' and flow.WORKFLOWSUBMITTIME >= '; 
       sqlStr:=start_date;
       sqlStr:=' and flow.WORKFLOWHANDLERTIME <= ';
       sqlStr:= end_date ;
       DBMS_OUTPUT.PUT_LINE(sqlStr);   
       execute immediate sqlStr;
       --想在这里将sqlStr的执行结果放入pList
     end;
     end loop;
--open pList for sqlStr;
end complaint_Detail;

Answer 1

你这个动态SQL写的本来就要问题，要用 sqlStr:= sqlStr||'';才能把字符串连接起来。另外，时间也不能直接用sqlStr:=sqlStr||start_date; ，因为这样会把日期当成字符串，会报错
给你个例子吧create or replace procedure PPMS_PT_Select_RGTrace(
                                                  intPlanId     in number,
                                                  intDict_PlanId in number,
                                                  intMonth       in number,
                                                  intDay         in number,
                                                  recordSet      out PPMS_PACKAGE.CurType) is
主要部分
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';--设置日期格式
strSql:='select CT.ItemId,CT.CateName,
              CT.SubCateName,CT.ItemName,
              nvl(PlanItem.VALUE1,0) Value1,
              nvl(PlanSplit.SplitAmount,0) Value2,
              nvl(ProduceItem.ProduceAmount,0) Value3,
              CT.CateId,CT.SubCateId
              from(select PCI.ITEMID ItemId,PC.CID CateId,
              PSC.sid SubCateId,
              PC.CNAME CateName,
              PSC.SUBCATENAME SubCateName,
              PI.ITEMNAME ItemName
              from ppms_act_planconfigitem PCI inner join ppms_dict_planitem PI on PCI.ITEMID=PI.ITEMID
              inner join ppms_dict_plansubcategory PSC on PI.SID=PSC.SID
              inner join ppms_dict_plancategory PC on PSC.CID=PC.CID
              where PCI.CONFID='||intConfigId||' and PC.PID=1)CT';strSql:=strSql||'left outer join (select * from ppms_act_plan_oilitem where planid='||intPlanId||' and id='||versionId||')PlanItem
              on CT.ItemId=PlanItem.Itemid              left outer join(select PT.ITEMID ItemId,sum(PT.VALUE) ProduceAmount from ppms_act_produce_oilitem PT
              where PT.planid '||strPlanId||' and PT.MODIFYDATETIME<=to_date('||strSearchDay||',''yyyy-mm-dd'')
              group by PT.ITEMID)ProduceItem on CT.ItemId=ProduceItem.ItemId              left outer join('||strSplitSql||') PlanSplit on CT.ItemId=PlanSplit.ItemId
              order by CT.SubCateId,CT.ItemId asc ';open recordSet for strSql;

Answer 2

倒，颜色搞乱了。strSearchDay是varchar2类型，如：'20090218';
再发一遍
create or replace procedure PPMS_PT_Select_RGTrace(
                                                  intPlanId     in number,
                                                  intDict_PlanId in number,
                                                  intMonth       in number,
                                                  intDay         in number,
                                                  recordSet      out PPMS_PACKAGE.CurType) is
主要部分
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd''';--设置日期格式
strSql:='select CT.ItemId,CT.CateName,
              CT.SubCateName,CT.ItemName,
              nvl(PlanItem.VALUE1,0) Value1,
              nvl(PlanSplit.SplitAmount,0) Value2,
              nvl(ProduceItem.ProduceAmount,0) Value3,
              CT.CateId,CT.SubCateId
              from(select PCI.ITEMID ItemId,PC.CID CateId,
              PSC.sid SubCateId,
              PC.CNAME CateName,
              PSC.SUBCATENAME SubCateName,
              PI.ITEMNAME ItemName
              from ppms_act_planconfigitem PCI inner join ppms_dict_planitem PI on PCI.ITEMID=PI.ITEMID
              inner join ppms_dict_plansubcategory PSC on PI.SID=PSC.SID
              inner join ppms_dict_plancategory PC on PSC.CID=PC.CID
              where PCI.CONFID='||intConfigId||' and PC.PID=1)CT';strSql:=strSql||'left outer join (select * from ppms_act_plan_oilitem where planid='||intPlanId||' and id='||versionId||')PlanItem
              on CT.ItemId=PlanItem.Itemid              left outer join(select PT.ITEMID ItemId,sum(PT.VALUE) ProduceAmount from ppms_act_produce_oilitem PT
              where PT.planid '||strPlanId||' and PT.MODIFYDATETIME<=to_date('||strSearchDay||',''yyyy-mm-dd'')
              group by PT.ITEMID)ProduceItem on CT.ItemId=ProduceItem.ItemId              left outer join('||strSplitSql||') PlanSplit on CT.ItemId=PlanSplit.ItemId
              order by CT.SubCateId,CT.ItemId asc ';open recordSet for strSql;

Answer 3

谢谢，那么将多条动态sql的执行结果放入指定的cursor的问题如何解决？像代码中在for循环里面拼sql
然后执行，一个关键性问题就是如何将每次sql的执行结果放入cursor