存儲(chǔ)過程中使用事務(wù)與try catch

一、存儲(chǔ)過程中使用事務(wù)的簡單語法

??????在存儲(chǔ)過程中使用事務(wù)時(shí)非常重要的，使用數(shù)據(jù)可以保持?jǐn)?shù)據(jù)的關(guān)聯(lián)完整性，在Sql server存儲(chǔ)過程中使用事務(wù)也很簡單，用一個(gè)例子來說明它的語法格式：

代碼?: Create Procedure MyProcedure

( @Param1 nvarchar(10),

@param2 nvarchar(10)

)

AS

Begin

Set NOCOUNT ON;

Set XACT_ABORT ON;

Begin Tran

Delete from table1?where?name=’abc’;

Insert into table2 values(value1,value2,value3);

Commit Tran

End

?

?

??????說明：1?、使用存儲(chǔ)過程執(zhí)行事物，需要開啟XACT_ABORT參數(shù)(默認(rèn)值為Off)，將該參數(shù)設(shè)置為On，表示當(dāng)執(zhí)行事務(wù)時(shí)，如果出錯(cuò),會(huì)將 transcation設(shè)置為uncommittable狀態(tài)，那么在語句塊批處理結(jié)束后將回滾所有操作；如果該參數(shù)設(shè)置為Off，表示當(dāng)執(zhí)行事務(wù)時(shí)，如果出錯(cuò)，出錯(cuò)的語句將不會(huì)執(zhí)行，其他正確的操作繼續(xù)執(zhí)行。

2、當(dāng)SET NOCOUNT 為 ON 時(shí)，不返回計(jì)數(shù)（計(jì)數(shù)表示受 Transact-SQL 語句影響的行數(shù)，例如在Sql server查詢分析器中執(zhí)行一個(gè)delete操作后，下方窗口會(huì)提示（3）Rows Affected）。當(dāng)???SET NOCOUNT 為 OFF 時(shí)，返回計(jì)數(shù),我們應(yīng)該在存儲(chǔ)過程的頭部加上SET NOCOUNT ON 這樣的話，在退出存儲(chǔ)過程的時(shí)候加上 SET NOCOUNT OFF這樣的話，以達(dá)到優(yōu)化存儲(chǔ)過程的目的。

?

二、事務(wù)內(nèi)設(shè)置保存點(diǎn)

　　用戶可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)定義如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置。如果將事務(wù)回滾到保存點(diǎn)，則必須（如果需要，使用更多的 Transact-SQL 語句和 COMMIT TRANSACTION 語句）繼續(xù)完成事務(wù)，或者必須（通過將事務(wù)回滾到其起始點(diǎn)）完全取消事務(wù)。若要取消整個(gè)事務(wù)，請(qǐng)使用 ROLLBACK TRANSACTION transaction_name 格式。這將撤消事務(wù)的所有語句和過程。如：

代碼 Create Procedure MyProcedure

AS

Begin

Set NOCOUNT ON;

Set XACT_ABORT ON;


begin tran ok?--開始一個(gè)事務(wù)OK

delete from rxqz?where?qz=?'rx015?'?--刪除數(shù)據(jù)

save tran bcd?--保存一個(gè)事務(wù)點(diǎn)命名為bcd

update sz?set?name='李麗s'?where?name=?'李麗'--修改數(shù)據(jù)

if?@@error<>0?--判斷修改數(shù)據(jù)有沒有出錯(cuò)

begin?--如果出錯(cuò)

rollback tran bcd?--?回滾事務(wù)到BCD 的還原點(diǎn)

commit tran ok?--提交事務(wù)

end

else?--沒有出錯(cuò)

commit tran ok?--提交事務(wù)

End

?

?

　　說明：1、@@error判斷是否有錯(cuò)誤，為0表示沒有錯(cuò)誤，但是對(duì)那種重大錯(cuò)誤無法捕捉，而且@@error只能前一句sql語句生效。

?

三、存儲(chǔ)過程使用try…catch捕獲錯(cuò)誤

　　在存儲(chǔ)過程中可以使用try…catch語句來捕獲錯(cuò)誤，如下：??

代碼 Create Procedure MyProcedure

( @Param1 nvarchar(10),

@param2 nvarchar(10)

)

AS

Begin

Set NOCOUNT ON;

Begin?try

Delete from table1?where?name=’abc’;

Insert into table2 values(value1,value2,value3);

End?try

Begin Catch

SELECT ERROR_NUMBER() AS ErrorNumber,

ERROR_MESSAGE() AS ErrorMessage;

End Catch

End

?

?

　　說明：1、捕獲錯(cuò)誤的函數(shù)有很多，如下：

???????????ERROR_NUMBER() 返回錯(cuò)誤號(hào)。

　　　　ERROR_SEVERITY() 返回嚴(yán)重性。

　　　　ERROR_STATE() 返回錯(cuò)誤狀態(tài)號(hào)。

　　　　ERROR_PROCEDURE() 返回出現(xiàn)錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。

　　　　ERROR_LINE() 返回導(dǎo)致錯(cuò)誤的例程中的行號(hào)。

　　　　ERROR_MESSAGE() 返回錯(cuò)誤消息的完整文本。該文本可包括任何可替換參數(shù)所提供的值，如長度、對(duì)象名或時(shí)間。

　　　　2、有些錯(cuò)誤，如sql語句中的表名稱輸入錯(cuò)誤，這是數(shù)據(jù)庫引擎無法解析這個(gè)表名稱時(shí)，所發(fā)生的錯(cuò)誤在當(dāng)前的try…catch語句中無法捕獲，必須由外層調(diào)用該存儲(chǔ)過程的地方使用　　　　　　　　　　　　???????????????try…catch來進(jìn)行捕獲。

?

四、存儲(chǔ)過程中事務(wù)和try…catch聯(lián)合使用

　　在存儲(chǔ)過程中使用事務(wù)時(shí)，如果沒有try…catch語句，那么當(dāng)set xact_abort on時(shí)，如果有錯(cuò)誤發(fā)生，在批處理語句結(jié)束后，系統(tǒng)會(huì)自動(dòng)回滾所有的sql操作。當(dāng)set xact_abort off時(shí)，如果有錯(cuò)誤發(fā)生，在批處理語句結(jié)束后，系統(tǒng)會(huì)執(zhí)行所有沒有發(fā)生錯(cuò)誤的語句，發(fā)生錯(cuò)誤的語句將不會(huì)被執(zhí)行。

在存儲(chǔ)過程中使用事務(wù)時(shí)，如果存在try…catch語句塊，那么當(dāng)捕獲到錯(cuò)誤時(shí)，需要在catch語句塊中手動(dòng)進(jìn)行Rollback操作，否則系統(tǒng)會(huì)給客戶端傳遞一條錯(cuò)誤信息。如果在存儲(chǔ)過程開始處將set xact_abort on，那么當(dāng)有錯(cuò)誤發(fā)生時(shí)，系統(tǒng)會(huì)將當(dāng)前事務(wù)置為不可提交狀態(tài)，即會(huì)將xact_state()置為-1，此時(shí)只可以對(duì)事務(wù)進(jìn)行Rollback操作，不可進(jìn)行提交(commit)操作，那么我們?cè)赾atch語句塊中就可以根據(jù)xact_state()的值來判斷是否有事務(wù)處于不可提交狀態(tài)，如果有則可以進(jìn)行rollback操作了。如果在存儲(chǔ)過程開始處將set xact_abort off，那么當(dāng)有錯(cuò)誤發(fā)生時(shí)，系統(tǒng)不會(huì)講xact_state()置為-1，那么我們?cè)赾atch塊中就不可以根據(jù)該函數(shù)值來判斷是否需要進(jìn)行 rollback了，但是我們可以根據(jù)@@Trancount全局變量來判斷，如果在catch塊中判斷出@@Trancount數(shù)值大于0，代表還有未提交的事務(wù)，既然進(jìn)入catch語句塊了，那么還存在未提交的事務(wù)，該事務(wù)應(yīng)該是需要rollback的，但是這種方法在某些情況下可能判斷的不準(zhǔn)確。推薦的方法還是將set xact_abort on，然后在catch中判斷xact_state()的值來判斷是否需要Rollback操作。

下面我們來看看兩個(gè)例子：

一．使用Set xact_abort???????on

?代碼

Create proc myProcedure

As

begin

set?xact_abort on;

begin?try

begin tran

insert into TestStu values('Terry','boy',23);

insert into TestStu values('Mary','girl',21);

commit tran

end?try

begin?catch

--在此可以使用xact_state()來判斷是否有不可提交的事務(wù),不可提交的事務(wù)

--表示在事務(wù)內(nèi)部發(fā)生錯(cuò)誤了。Xact_state()有三種值：-1.事務(wù)不可提交；

--1.事務(wù)可提交；0.表示沒有事務(wù)，此時(shí)commit或者rollback會(huì)報(bào)錯(cuò)。

if?xact_state()=-1

rollback tran;

end?catch

end

?

?

?

二.使用Set xact_abort off

?

代碼 Create proc myProcedure

As

begin

set?xact_abort off;

begin?try

begin tran

insert into TestStu values('Terry','boy',23);

insert into TestStu values('Mary','girl',21);

commit tran

end?try

begin?catch

--在此不可以使用xact_state來判斷是否有不可提交的事務(wù)

--只可以使用@@Trancount來判斷是否有還未提交的事務(wù)，未提交的事務(wù)未必

--就是不可提交的事務(wù)，所以使用@@TranCount>0后就RollBack是不準(zhǔn)確的

if?@@TranCount>0

rollback tran;

end?catch

end

?

?????另外，對(duì)于@@Trancount需要說明的是，begin??tran 語句將 @@Trancount加 1。Rollback??tran將 @@Trancount遞減到 0，但 Rollback tran savepoint_name 除外，它不影響 @@Trancount。Commit ?tran 或 Commit??work 將 @@Trancount 遞減 1。

?

轉(zhuǎn)載:http://blog.sina.com.cn/s/blog_6f7c75ad0100uqcy.html