PROCEDURE proc_check_int( --检查利息是否入帐
p_i_sdate IN VARCHAR2,
p_i_edate IN VARCHAR2,
P_O_MSG OUT VARCHAR2
)
IS
CURSOR cur_int IS
SELECT * From NFCHINT
where accno not like '12__00000000000%'
FOR UPDATE of JHZONENO;
........................................... BEGIN
FOR row_set IN cur_int LOOP
BEGIN
V_ACCNO:=row_set.ACCNO;
v_count := v_count +1;
Select AMOUNT into V_AMOUNT
From NFCKLST
Where ACCNO=row_set.ACCNO
AND AMOUNT=row_set.AMOUNT
AND DRCRF='2'
AND BUSIDATE between p_i_sdate and p_i_edate
; EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE=100 then
update NFCHINT set JHZONENO='N'
WHERE CURRENT OF cur_int;
v_num:=v_num+1;
end if;
End;
IF v_count MOD 10000 = 0
THEN
insert into w_BatchTag values(sysdate, v_count,v_num);
-- COMMIT; ●●●●为什么这里不能用COMMIT?
END IF;
END LOOP;
p_o_msg:='OK';
insert into w_BatchTag values(sysdate, v_count,v_num);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error!......'); p_o_msg :=
'过程[proc_proess]中发生未知异常,[ACCno='
|| V_ACCNO
|| '],[sqlcode='
|| SQLCODE
|| ']';
DBMS_OUTPUT.PUT_LINE(p_o_msg);
ROLLBACK;
END;
p_i_sdate IN VARCHAR2,
p_i_edate IN VARCHAR2,
P_O_MSG OUT VARCHAR2
)
IS
CURSOR cur_int IS
SELECT * From NFCHINT
where accno not like '12__00000000000%'
FOR UPDATE of JHZONENO;
........................................... BEGIN
FOR row_set IN cur_int LOOP
BEGIN
V_ACCNO:=row_set.ACCNO;
v_count := v_count +1;
Select AMOUNT into V_AMOUNT
From NFCKLST
Where ACCNO=row_set.ACCNO
AND AMOUNT=row_set.AMOUNT
AND DRCRF='2'
AND BUSIDATE between p_i_sdate and p_i_edate
; EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE=100 then
update NFCHINT set JHZONENO='N'
WHERE CURRENT OF cur_int;
v_num:=v_num+1;
end if;
End;
IF v_count MOD 10000 = 0
THEN
insert into w_BatchTag values(sysdate, v_count,v_num);
-- COMMIT; ●●●●为什么这里不能用COMMIT?
END IF;
END LOOP;
p_o_msg:='OK';
insert into w_BatchTag values(sysdate, v_count,v_num);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error!......'); p_o_msg :=
'过程[proc_proess]中发生未知异常,[ACCno='
|| V_ACCNO
|| '],[sqlcode='
|| SQLCODE
|| ']';
DBMS_OUTPUT.PUT_LINE(p_o_msg);
ROLLBACK;
END;
end loop后面试试
end loop后面试试补充一下:
在你原来使用commit的地方,改成 savepoint aa; 以便达到你分段提交的目的。