游标-遍历-查询-1

declare
       --类型定义
       strsql string(500);
       type prizeinfo is record(
       prizecode VARCHAR2(200),
       prizetypeid number,
       prizename VARCHAR2(200),
       description VARCHAR2(500),
       packageid number,
       campaignid number
       );
       type prizecode is record(
        prizecode VARCHAR2(200),
        rowcounts number
       );
       info prizeinfo;
       codeinfo prizecode;
       rows_data VARCHAR2(200);
        v_ErrorCode NUMBER;           -- Variable to hold the error message code   
        v_ErrorText VARCHAR2(200);    -- Variable to hold the error message text   
       cursor c_eventstate
       is
       Select  CampaignId,phonenumber,PolicyNumber,packageid from mkt_eventstate a where synstate =0 and packagestate = 2 and errorcode = 6 and rownum<10000 ;
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_eventstate%rowtype;
begin
       for c_row in c_eventstate loop
          begin  
         -- strsql:='select a.prizecode,a.prizetypeid,b.prizename,b.description,c.packageid,c.campaignid from  mkt_CampaignPrize a join mkt_prizeType b on a.prizetypeid=b.prizetypeid 
             --      join mkt_UserPrizeInfo c on  a.CampaignPrizeId=c.CampaignPrizeId and    c.campaignid='||c_row.CampaignId||' and c.userid='||c_row.phonenumber||' and c.PolicyNumber='''||c_row.PolicyNumber||''' 
              --     join  mkt_packageprize e on e.packageid='||c_row.packageid||' and a.prizetypeid=e.prizetypeid and rownum=1 and  b.PROVIDER=''生活服务商城''' ;
         -- execute immediate strsql into info  ;
        --  dbms_output.put_line(info.prizecode||'-'||info.prizename||'-'||info.packageid||'-'||info.campaignid);
          
           strsql:='select a.prizecode, count(1) from  mkt_CampaignPrize a join mkt_prizeType b on a.prizetypeid=b.prizetypeid 
                   join mkt_UserPrizeInfo c on  a.CampaignPrizeId=c.CampaignPrizeId and    c.campaignid='||c_row.CampaignId||' and c.userid='||c_row.phonenumber||' and c.PolicyNumber='''||c_row.PolicyNumber||''' 
                   join  mkt_packageprize e on e.packageid='||c_row.packageid||' and a.prizetypeid=e.prizetypeid  and  b.PROVIDER=''生活服务商城'' group by a.prizecode, a.prizetypeid having count(1)>1' ;
          execute immediate strsql into codeinfo  ;
          dbms_output.put_line(codeinfo.prizecode||'-'||codeinfo.rowcounts);
        -- dbms_output.put_line(c_row.CampaignId||'-'||c_row.phonenumber||'-'||c_row.PolicyNumber);
        -- RAISE_APPLICATION_ERROR(-20001,'数值不能为0');
          EXCEPTION
         --捕捉异常
         WHEN others THEN
           begin
              v_ErrorCode := SQLCODE;   
              v_ErrorText := SUBSTR(SQLERRM, 1, 200);  
           DBMS_OUTPUT.put_line('捕获了错误begin');
           dbms_output.put_line(c_row.CampaignId||'-'||c_row.phonenumber||'-'||c_row.PolicyNumber||'-'||v_ErrorCode || '::'||v_ErrorText);
           DBMS_OUTPUT.put_line('捕获了错误end');
           end;
         end;
        
       end loop;     
end;