Cursors-Traversal-query-1

Declare--type definition strSQL string (500); Type Prizeinfo is record (Prizecode VARCHAR2 ($), Prizetypeid number, Prizename VARCHAR2 ($), DE       Scription VARCHAR2 (+), PackageID number, campaignid number);       Type Prizecode is record (Prizecode VARCHAR2 ($), 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,poli       Cynumber,packageid from Mkt_eventstate a where synstate =0 and packagestate = 2 and ErrorCode = 6 and rownum<10000; --Define a cursor variable v_cinfo c_emp%rowtype, which is a row of data type in cursor c_emp c_row c_eventstate%rowtype;begin for C_row in C_even Tstate Loop Begin--strsql:= ' select A.prizecode,a.prizetypeid,B.prizename,b.description,c.packageid,c.campaignid from Mkt_campaignprize a joins 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= ' Life Service Mall ';        --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 joins 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= ' Life Service Mall ' GROUP by A.prizecode, A.prizetypeid have          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, ' value cannot be 0 ');                 EXCEPTION-Catch exception when others then BEGIN v_errorcode: = SQLCODE;             V_errortext: = SUBSTR (SQLERRM, 1, 200);           Dbms_output.put_line (' Caught the wrong begin '); Dbms_output.put_line (C_row. campaignid| | ' -' | | c_row.phonenumber| | ' -' | | C_row. policynumber| | ' -' | | V_errorcode | | ':: ' | |           V_errortext);           Dbms_output.put_line (' Catch error end ');         End               End     End Loop; End

Cursors-Traversal-query-1