oracle游标能存多大,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*100 ) Order By machinenum ;

Begin

v_APPID := 1;

Select trunc(Count(1)/100)+1 Into v_count From BSAAPP.MSTB_CRM_REGISTERS;

For i In 0.. v_count Loop

c_count :=i;

For v1 In c1 Loop

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 ;