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

古城微笑少年丶 2023-01-18 04:29 103阅读 0赞

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;

\--判断是否已经有了顾客

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);

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;