请教 优化存储过程

Question

CREATE OR REPLACE Procedure PRO_COL_CustomerInCut2 AS
Begin
Declare
iRow1 Integer;
iRow2 Integer;
dtToday Date;
sToday CHAR(14);
J Integer;
sCustIdTemp Col_Customer_Def.sCustId%Type;
Cursor C1 IS
Select * from Col_VM_CustIn_CustDef order by sCustId;
Begin
Select SYSDATE into dtToday from Dual;
Select TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') into sToday from Dual;
sCustIdTemp := ' ';
For I1 In C1 Loop
If I1.sCustId <> sCustIdTemp then
sCustIdTemp := I1.sCustId;
Insert into Col_Customer_Def 
(
sId, dtBirthday, sBillZip, sBillAddr, sCustZip,
sCustAddr, sHomeTel, sPhone, sCorpName, sCorpTel,
sContactName, sContactTel, sCustId, sCustNo, sCustName,
sSopCustNo, sAgent, sAgentTel, sAgentId, sAgentZip,
sAgentAddr, sCompId, sPayMd, sHomeZip, sHomeAddr,
dtEntTime, sEntRec, iProcessFlag
)
values
(
I1.sId, I1.dtBirthday, I1.sBillZip, I1.sBillAddr, I1.sCustZip,
I1.sCustAddr, I1.sHomeTel, I1.sPhone, I1.sCorpName, I1.sCorpTel,
I1.sContactName,I1.sContactTel, I1.sCustId, I1.sCustNo, I1.sCustName,
I1.sSopCustNo, I1.sAgent, I1.sAgentTel, I1.sAgentId, I1.sAgentZip,
I1.sAgentAddr, I1.sCompId, I1.sPayMd, I1.sHomeZip, I1.sHomeAddr,
sToday, '000000000000', 1
);
J := J + 1;
If J >= 10000 then
J := 0;
commit;
End If;
End If;
End Loop;
commit;
Delete Col_Customer_In;
End;
End;
/

Answer 1

Insert into Col_Customer_Def 
(
sId,dtBirthday,sBillZip,sBillAddr,sCustZip,
sCustAddr,sHomeTel,sPhone,sCorpName,sCorpTel,
sContactName,sContactTel,sCustId,sCustNo,sCustName,
sSopCustNo,sAgent,sAgentTel,sAgentId,sAgentZip,
sAgentAddr,sCompId,sPayMd,sHomeZip,sHomeAddr,
dtEntTime,sEntRec,iProcessFlag
)
values
(
I1.sId,I1.dtBirthday,I1.sBillZip,I1.sBillAddr,I1.sCustZip,
I1.sCustAddr,I1.sHomeTel,I1.sPhone,I1.sCorpName,I1.sCorpTel,
I1.sContactName,I1.sContactTel,I1.sCustId,I1.sCustNo,I1.sCustName,
I1.sSopCustNo,I1.sAgent,I1.sAgentTel,I1.sAgentId,I1.sAgentZip,
I1.sAgentAddr,I1.sCompId,I1.sPayMd,I1.sHomeZip,I1.sHomeAddr,
这一段可以用
INSERT INTO Col_Customer_Def 
SELECT FROM I1 WEHRE .... 
来完成.

Answer 2

楼主是想把sCustId 相同的记录合成一条对吧？
表的规模是多少呢？

Answer 3

whqcfp(one_dollar)谢谢了，但是Col_Customer_Def 表不只是那些字段，不能直接insert

Answer 4

成批邦定（8i及以上版本支持）,bulk collect试试

Answer 5

循环采用forall！不用cursor。应该快很多

Answer 6

for I1 in (select * from )loop
end loop;

Answer 7

for I1 in (select * from )loop
end loop;能加快速度嗎？