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;
解决方案 »
- rownum和rowid有什么区别
- SELECT A.SiHai_CODE, @@@@@ form table这里的@@@@@ 是什么
- imp导入操作进行到一半ctrl+C退出,下步该怎么?
- fedora 9 上oracle 10g 的em上的按钮显示乱码
- Oracle中function相关问题
- 【问个弱问题】sql语句中乘方运算和求积运算分别怎么写?
- ORA-12154: TNS:could not resolve service name
- 登录SQL*PLUS????
- 在IMP时怎么样以SYSDBA来登录
- 如何使用plsql实现多表的删除?!
- long字段的调用与显示问题?
- 关于RANK OVER报错的问题
end loop后面试试
end loop后面试试补充一下:
在你原来使用commit的地方,改成 savepoint aa; 以便达到你分段提交的目的。