oracle存储过程中需要循环的游标数据量过大 的解决办法

create or replace procedure ProcessLegacyData Is  v_count Number;    --总记录数  c_count Number;   --循环的次数  v_APPID Number;          --香港或者大陆  --v_ada      Varchar2(30);    --顾客的ada  v_errmsg  Varchar2(300);   --顾客的手机号码  v_customer  bsaapp.mstb_crm_customer%Rowtype;  v_c Number;--查看数据是否存在  c_SERVERID   VARCHAR2(36);  c_TERMINALID VARCHAR2(36);  v_r          Number;   Cursor c1 Is     select * from MSTB_CRM_REGISTERS where rowid in(select rid from (select rownum rn,rid from(select rowid rid,machinenum From MSTB_CRM_REGISTERS Order By machinenum) where rownum<(c_count+1)*100) where rn>=c_count*100 ) Order By machinenum ;Begin     v_APPID := 1;     Select trunc(Count(1)/100)+1 Into v_count From BSAAPP.MSTB_CRM_REGISTERS;    <span style="color:#ff0000;"> For i In 0.. v_count Loop         c_count :=i;       For v1 In c1 Loop</span>       Begin        --根据ada或者手机号码查找顾客         if v1.ada Is Null  Then            Select count(1) Into v_c from bsaapp.mstb_crm_customer Where PHONENUM = v1.mobilenum ;           if v_c>0 Then             Select * Into v_customer from bsaapp.mstb_crm_customer Where PHONENUM = v1.mobilenum ;           end if;         Else           Select count(1) Into v_c from bsaapp.mstb_crm_customer Where ada = v1.ada ;           if v_c>0 Then             Select * Into v_customer from bsaapp.mstb_crm_customer Where ada = v1.ada ;           end if;         end if;         --判断是否已经有了顾客          if v_c>0 Then              Insert Into BSAAPP.MSTB_CRM_REGISTER(SERVERID,TERMINALID,APP_ID,OWNERADA,STATUS,CNAME,ENAME,PROV_CODE,CITY_CODE,TOWN_CODE,PROV_NAME,CITY_NAME,TOWN_NAME,ADDR,MOBILENUM,PHONENUM,EMAIL,MACHINENUM,REGISTER_CHANNEL,INSTALLDATE,ADA,PRODUCTCODE,ISPUBILC,CUSTOMER_SERVERID,CUSTOMER_TERMINALID,SERVER_CREATEDATE,TERMINAL_CREATEDATE,UPDATEDATE)                     Values(sys_guid(),sys_guid(),V_APPID,v1.ownerada,0,v1.cname,v1.ename,v1.prov_code,v1.city_code,v1.town_code,v1.prov_name,v1.city_name,v1.Town_Name,v1.addr,v1.mobilenum,v1.phonenum,v1.email,v1.machinenum,v1.register_channel,v1.installdate,v1.ada,v1.productcode,v1.Ispubilc,v_customer.serverid,v_customer.terminalid,sysdate,Sysdate,Sysdate);              if v1.productcode = 'WTS' Then                 v_r:=  saveWTSEvent(v_customer.serverid,v_customer.terminalid,v1.machinenum,V_APPID,v1.ownerada,to_char(v1.installdate,'yyyy-mm-dd'));             Else                 v_r:=  saveATSEvent(v_customer.serverid,v_customer.terminalid,v1.machinenum,V_APPID,v1.ownerada,to_char(v1.installdate,'yyyy-mm-dd'));              End If;                           Else            --如果找不到顾客就新建顾客和关怀信息            c_SERVERID   :=sys_guid();            c_TERMINALID :=sys_guid();             Insert Into bsaapp.mstb_crm_customer(SERVERID,TERMINALID,APP_ID,ADA,OWNERADA,STATUS,NAME,PIC,PHONENUM,SERVER_CREATEDATE,TERMINAL_CREATEDATE,UPDATEDATE,NAME_FIRSTLETTER,NAME_PINYIN)                                                                      Values(c_SERVERID,c_TERMINALID,v_APPID,v1.ada,v1.OWNERADA,0,v1.cname,Null,v1.mobilenum,Sysdate,Sysdate,Sysdate,Null,Null);             Insert Into bsaapp.mstb_crm_customerinteractive(SERVERID,TERMINALID,APP_ID,OWNERADA,STATUS,CARE_DATE,TYPE,CUSTOMER_SERVERID,CUSTOMER_TERMINALID,UPDATEDATE)                    Values(sys_guid(),sys_guid(),v_APPID,v1.ownerada,0,Sysdate,99,c_SERVERID,c_TERMINALID,Sysdate);              Insert Into BSAAPP.MSTB_CRM_REGISTER(SERVERID,TERMINALID,APP_ID,OWNERADA,STATUS,CNAME,ENAME,PROV_CODE,CITY_CODE,TOWN_CODE,PROV_NAME,CITY_NAME,TOWN_NAME,ADDR,MOBILENUM,PHONENUM,EMAIL,MACHINENUM,REGISTER_CHANNEL,INSTALLDATE,ADA,PRODUCTCODE,ISPUBILC,CUSTOMER_SERVERID,CUSTOMER_TERMINALID,SERVER_CREATEDATE,TERMINAL_CREATEDATE,UPDATEDATE)                     Values(sys_guid(),sys_guid(),V_APPID,v1.ownerada,0,v1.cname,v1.ename,v1.prov_code,v1.city_code,v1.town_code,v1.prov_name,v1.city_name,v1.Town_Name,v1.addr,v1.mobilenum,v1.phonenum,v1.email,v1.machinenum,v1.register_channel,v1.installdate,v1.ada,v1.productcode,v1.Ispubilc,c_SERVERID,c_TERMINALID,sysdate,Sysdate,Sysdate);             if v1.productcode ='WTS' Then                v_r:= saveWTSEvent(c_SERVERID,c_TERMINALID,v1.machinenum,V_APPID,v1.ownerada,to_char(v1.installdate,'yyyy-mm-dd'));                Else                v_r:= saveATSEvent(c_SERVERID,c_TERMINALID,v1.machinenum,V_APPID,v1.ownerada,to_char(v1.installdate,'yyyy-mm-dd'));              End If;           end if;         Commit;         Exception          When Others Then          v_errmsg :=v1.machinenum || Sqlerrm ;            Insert Into mstb_crm_error(id) Values(v_errmsg);        End;       End Loop;          End Loop;          Commit;end ProcessLegacyData ;