如何在oracle中处理异常并像sql一样回滚Transcation [英] How to handle exceptions in oracle and Roll back the Transcation same like sql

问题描述

亲爱的朋友们，



创建或替换PROCEDURESP_INSERTG

（

ID_ NUMBER，< br $>
NAME_ nvarchar2，

CDATE_ timestamp，

MDATE_ timestamp，

输出NUMBER

）

AS

BEGIN - 子块开始

--SAVEPOINT start_transaction; - 标记保存点

插入tbl_G（GID，NAME，DDATE，DSTATE，MODIFIEDDATE）值（ID_，NAME_，CDATE _，'0'，MDATE_）;

IF（SQL％ROWCOUNT> = 1） - 它的返回0或1

那么

输出：= 1; - 输出1

ELSE

输出：= 0; - 输出0

END IF;

NULL;



结束SP_INSERTG;



这是我在Oracle中的我的存储过程处理异常并回滚Transcation。如果有任何想法的人请与我分享。





问候，



AnilKumar.D

Dear Friends,

create or replace PROCEDURE "SP_INSERTG"
(
ID_ NUMBER,
NAME_ nvarchar2,
CDATE_ timestamp,
MDATE_ timestamp,
output out NUMBER
)
AS
BEGIN -- sub-block begins
--SAVEPOINT start_transaction; -- mark a savepoint
Insert into tbl_G (GID,NAME,DDATE,DSTATE,MODIFIEDDATE) values(ID_,NAME_,CDATE_,'0',MDATE_);
IF( SQL%ROWCOUNT >= 1 )-- its Return 0 or 1
THEN
output := 1; --Output 1
ELSE
output := 0;-- Output 0
END IF;
NULL;

END SP_INSERTG;

This is My StoredProcedure in Oracle iam new to Oracle in this Sp i want to handle the Exceptions and Roll back the Transcation.If any body having idea please share with me.


Regards,

AnilKumar.D

推荐答案

在 END SP_INSERTG;



add following block before END SP_INSERTG;

COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK TO start_transaction;

请参阅http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/errors.htm#i3336 [ ^ ]进一步参考。



问候，

Niral Soni

Refer to http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/errors.htm#i3336[^] for further reference.

Regards,
Niral Soni

这篇关于如何在oracle中处理异常并像sql一样回滚Transcation的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！