【问题背景】 2013-08-02 为某地市做了1个脚本用于帮客户账户添加一个新的账本。犯了一个很二的错，存储过程如下（SQL记录用户以后查询），一晚上只执行了190W数据 脚本如下 数据库中总共有500W或者更多的客户。
【问题原因】 但由于每次循环时都COMMIT提交，导致效率低，
相比之下，每1W条提交，肯定是更有效率的
因为每一次commit会触发LGWR做很多相应的操作，将重做BUFFER中的数据写到日志当中，然后去处理回滚段的相应信息，让回滚段中900秒（默认）之外的信息可以被重写。
也就是每一次commit都会带来大量的操作。
但是，commit的数据，也并不是全部完成之后提交一次效率就高了，因为还要看data_buffer的大小。
commit与数据写DBF的时机是没啥关系的，那个取决于CKPT.
因为每一次commit会触发LGWR做很多相应的操作，将重做BUFFER中的数据写到日志当中，然后去处理回滚段的相应信息，让回滚段中900秒（默认）之外的信息可以被重写。
也就是每一次commit都会带来大量的操作。
但是，commit的数据，也并不是全部完成之后提交一次效率就高了，因为还要看data_buffer的大小。
commit与数据写DBF的时机是没啥关系的，那个取决于CKPT.
【解决方法】 分批次处理 ： 例如处理完10000个客户后才提交一次，使用MOD除法函数实现
【效率低的问题脚本】
--20130802
--liuyong
--修正信息: 1-普通预存款余额
create or replace procedure prc_createacctbook is
cursor cur_account is
select *
from accounten a
where a.isdefaultid = 1
and a.businessid = 2
and not exists (select *
from acctbooken ac
where a.accountid_pk = ac.objid
and ac.balancetypeid_pk = 1);
V_ACCOUNTID NUMBER(8);
V_ACCOUNTCODE VARCHAR2(50);
V_ACCOUNTNAME VARCHAR2(70);
V_CUSTOMERID NUMBER(8);
V_MEN varchar2(50);
V_OBJTYPEID NUMBER(2);
V_BALANCETYPEID NUMBER(5);
V_BALANCETYPENAME VARCHAR2(50);
V_account_book_name VARCHAR2(70);
begin
V_MEN := 'p20130802LY'; --补丁记录备注
V_OBJTYPEID := '1'; --对象类型为账户
V_BALANCETYPEID := '1';
V_BALANCETYPENAME := '普通预存款余额';
--ADD ACCTBOOK
for vv_account in cur_account loop
V_ACCOUNTID := vv_account.accountid_pk;
V_ACCOUNTCODE := vv_account.accountcodestr;
V_ACCOUNTNAME := vv_account.accountnamestr;
V_CUSTOMERID := vv_account.customerid_pk;
--设置余额账本名称
if (length(concat(V_ACCOUNTNAME, V_BALANCETYPENAME)) > 25) then
V_account_book_name := V_ACCOUNTNAME;
else
V_account_book_name := concat(V_ACCOUNTNAME, V_BALANCETYPENAME);
end if;
--添加账本
insert into acctbooken
(ACCTBOOKID_PK,
BALANCETYPEID_PK,
ACCTBOOKNAMESTR,
ACCTBOOKCODESTR,
STARTDT,
BALANCEID,
CYCLE_UPPERID,
CYCLE_LOWERID,
STATUSID,
CREATEDT,
MEM,
CUSTOMERID,
OBJTYPEID,
OBJID)
values
(seq_acctbooken.nextval, --ID
1,
V_account_book_name, --accountname+typename
CONCAT(V_ACCOUNTCODE, V_BALANCETYPEID), --accountcode+typecode
to_date('19700101', 'yyyymmdd'),
'0',
'0',
'0',
1,
sysdate,
V_MEN,
V_CUSTOMERID,
V_OBJTYPEID,
V_ACCOUNTID);
--添加余额对象关系
insert into ACCTBALANCEOBJEN
(ACCBALANCEOBJID_PK,
ACCTBOOKID_PK,
OBJTYPEID,
OBJID,
MEM,
CREATEDT,
STATUSID)
values
(SEQ_ACCTBALANCEOBJEN.NEXTVAL,
seq_acctbooken.currval,
V_OBJTYPEID,
V_ACCOUNTID,
V_MEN,
sysdate,
1);
update payprojecten po
set po.statusid = 0, po.mem = V_MEN || po.mem
where po.accountid_pk = V_ACCOUNTID;
insert into PAYPROJECTEN
(PAYPROJECTID_PK,
PAYMETHODID_PK,
ACCOUNTID_PK,
ACCTBOOKID_PK,
PAYTYPEID,
PRIID,
STATUSID,
CREATEDT,
MEM)
values
(SEQ_PAYPROJECTEN.Nextval,
'111', --paymathod.cash
V_ACCOUNTID,
seq_acctbooken.currval,
1,
0,
1,
sysdate,
V_MEN);
COMMIT;
end loop;
end;
/
【修改后脚本如下】
--20130802
create or replace procedure prc_createacctbook is
cursor cur_account is
select *
from accounten a
where a.isdefaultid = 1
and a.businessid = 2
and not exists (select *
from acctbooken ac
where a.accountid_pk = ac.objid
and ac.balancetypeid_pk = 1);
V_ACCOUNTID NUMBER(8);
V_ACCOUNTCODE VARCHAR2(50);
V_ACCOUNTNAME VARCHAR2(70);
V_CUSTOMERID NUMBER(8);
V_MEN varchar2(50);
V_OBJTYPEID NUMBER(2);
V_BALANCETYPEID NUMBER(5);
V_BALANCETYPENAME VARCHAR2(50);
V_account_book_name VARCHAR2(70);
V_NUM NUMBER;
begin
V_MEN := 'p20130802LY'; --补丁记录备注
V_OBJTYPEID := '1';
V_BALANCETYPEID := '1';
V_BALANCETYPENAME := '普通预存款余额';
V_NUM := 0; --
--ADD ACCTBOOK
for vv_account in cur_account loop
V_ACCOUNTID := vv_account.accountid_pk;
V_ACCOUNTCODE := vv_account.accountcodestr;
V_ACCOUNTNAME := vv_account.accountnamestr;
V_CUSTOMERID := vv_account.customerid_pk;
if (length(concat(V_ACCOUNTNAME, V_BALANCETYPENAME)) > 25) then
V_account_book_name := V_ACCOUNTNAME;
else
V_account_book_name := concat(V_ACCOUNTNAME, V_BALANCETYPENAME);
end if;
insert into acctbooken
(ACCTBOOKID_PK,
BALANCETYPEID_PK,
ACCTBOOKNAMESTR,
ACCTBOOKCODESTR,
STARTDT,
BALANCEID,
CYCLE_UPPERID,
CYCLE_LOWERID,
STATUSID,
CREATEDT,
MEM,
CUSTOMERID,
OBJTYPEID,
OBJID)
values
(seq_acctbooken.nextval, --ID
1,
V_account_book_name, --accountname+typename
CONCAT(V_ACCOUNTCODE, V_BALANCETYPEID), --accountcode+typecode
to_date('19700101', 'yyyymmdd'),
'0',
'0',
'0',
1,
sysdate,
V_MEN,
V_CUSTOMERID,
V_OBJTYPEID,
V_ACCOUNTID);
insert into ACCTBALANCEOBJEN
(ACCBALANCEOBJID_PK,
ACCTBOOKID_PK,
OBJTYPEID,
OBJID,
MEM,
CREATEDT,
STATUSID)
values
(SEQ_ACCTBALANCEOBJEN.NEXTVAL,
seq_acctbooken.currval,
V_OBJTYPEID,
V_ACCOUNTID,
V_MEN,
sysdate,
1);
update payprojecten po
set po.statusid = 0, po.mem = V_MEN || po.mem
where po.accountid_pk = V_ACCOUNTID;
insert into PAYPROJECTEN
(PAYPROJECTID_PK,
PAYMETHODID_PK,
ACCOUNTID_PK,
ACCTBOOKID_PK,
PAYTYPEID,
PRIID,
STATUSID,
CREATEDT,
MEM)
values
(SEQ_PAYPROJECTEN.Nextval,
'111', --paymathod.cash
V_ACCOUNTID,
seq_acctbooken.currval,
1,
0,
1,
sysdate,
V_MEN);
-- COMMIT; 注销提交，修改为10000提交一次
V_NUM := V_NUM + 1;
IF MOD(V_NUM, 10000) = 0 THEN
COMMIT; --除10000 为0时提交
END IF;
end loop;
end;
/