存储过程里有两条DML语句,需要每条都ROOLBACK吗?
小弟的存储过程有两个DML语句,我担心前一个执行一个成功了,后面了失败的情况,就在每个DML语句
之后都加上ROOLBACK语句,需要吗,有一种说法是:如果只有一句DML语句的话可以不用加ROLLBACK
(我的理解是不是ORACLE 会隐式ROOLBACK)
如果有两条以上DML操作的话,就需要加上了,我在两句DML语句都没有异常的情况写
一并COMMIT的,大家看我下面贴上的写法严谨吗,如果不合适,请指教!!!
BEGIN
......
......
......
BEGIN
UPDATE CUR_TRAN_RECORD
SET LASTUPDATE = TO_CHAR(SYSDATE,'YYYYMMDD'),
STARTDATE = I_STARTDATE,
ENDDATE = I_ENDDATE
WHERE ACCNO = I_ACCNO
AND SUBACC = I_SUBACC
AND (STARTDATE IS NULL AND ENDDATE IS NULL);
EXCEPTION
WHEN OTHERS THEN
O_RETCODE :='EBPT070001'|| '*' ||'errorcode:'||sqlcode||' errmessage:'||sqlerrm;
ROLLBACK;
RETURN;
END;
BEGIN
DELETE FROM CUR_TRAN_RECORD
WHERE ACCNO = I_ACCNO
AND SUBACC = I_SUBACC
AND ( STARTDATE <> I_STARTDATE OR ENDDATE <> I_ENDDATE);
EXCEPTION
WHEN OTHERS THEN
O_RETCODE :='EBPT070002'|| '*' ||'errorcode:'||sqlcode||' errmessage:'||sqlerrm;
ROLLBACK;
RETURN;
END;
COMMIT;
......
......
......
END CORE_QUERY;