存儲(chǔ)過(guò)程中使用事務(wù)與try catch
一、存儲(chǔ)過(guò)程中使用事務(wù)的簡(jiǎn)單語(yǔ)法
??????在存儲(chǔ)過(guò)程中使用事務(wù)時(shí)非常重要的，使用數(shù)據(jù)可以保持?jǐn)?shù)據(jù)的關(guān)聯(lián)完整性，在Sql server存儲(chǔ)過(guò)程中使用事務(wù)也很簡(jiǎn)單，用一個(gè)例子來(lái)說(shuō)明它的語(yǔ)法格式：
代碼?: 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
?
?
??????說(shuō)明：1?、使用存儲(chǔ)過(guò)程執(zhí)行事物，需要開(kāi)啟XACT_ABORT參數(shù)(默認(rèn)值為Off)，將該參數(shù)設(shè)置為On，表示當(dāng)執(zhí)行事務(wù)時(shí)，如果出錯(cuò),會(huì)將 transcation設(shè)置為uncommittable狀態(tài)，那么在語(yǔ)句塊批處理結(jié)束后將回滾所有操作；如果該參數(shù)設(shè)置為Off，表示當(dāng)執(zhí)行事務(wù)時(shí)，如果出錯(cuò)，出錯(cuò)的語(yǔ)句將不會(huì)執(zhí)行，其他正確的操作繼續(xù)執(zhí)行。
2、當(dāng)SET NOCOUNT 為 ON 時(shí)，不返回計(jì)數(shù)（計(jì)數(shù)表示受 Transact-SQL 語(yǔ)句影響的行數(shù)，例如在Sql server查詢(xún)分析器中執(zhí)行一個(gè)delete操作后，下方窗口會(huì)提示（3）Rows Affected）。當(dāng)???SET NOCOUNT 為 OFF 時(shí)，返回計(jì)數(shù),我們應(yīng)該在存儲(chǔ)過(guò)程的頭部加上SET NOCOUNT ON 這樣的話(huà)，在退出存儲(chǔ)過(guò)程的時(shí)候加上 SET NOCOUNT OFF這樣的話(huà)，以達(dá)到優(yōu)化存儲(chǔ)過(guò)程的目的。
?
二、事務(wù)內(nèi)設(shè)置保存點(diǎn)
用戶(hù)可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)定義如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置。如果將事務(wù)回滾到保存點(diǎn)，則必須（如果需要，使用更多的 Transact-SQL 語(yǔ)句和 COMMIT TRANSACTION 語(yǔ)句）繼續(xù)完成事務(wù)，或者必須（通過(guò)將事務(wù)回滾到其起始點(diǎn)）完全取消事務(wù)。若要取消整個(gè)事務(wù)，請(qǐng)使用 ROLLBACK TRANSACTION transaction_name 格式。這將撤消事務(wù)的所有語(yǔ)句和過(guò)程。如：
代碼 Create Procedure MyProcedureAS
Begin
Set NOCOUNT ON;
Set XACT_ABORT ON;
begin tran ok?--開(kāi)始一個(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ù)有沒(méi)有出錯(cuò)
begin?--如果出錯(cuò)
rollback tran bcd?--?回滾事務(wù)到BCD 的還原點(diǎn)
commit tran ok?--提交事務(wù)
end
else?--沒(méi)有出錯(cuò)
commit tran ok?--提交事務(wù)
End
?
?
說(shuō)明：1、@@error判斷是否有錯(cuò)誤，為0表示沒(méi)有錯(cuò)誤，但是對(duì)那種重大錯(cuò)誤無(wú)法捕捉，而且@@error只能前一句sql語(yǔ)句生效。
?
三、存儲(chǔ)過(guò)程使用try…catch捕獲錯(cuò)誤
在存儲(chǔ)過(guò)程中可以使用try…catch語(yǔ)句來(lái)捕獲錯(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
?
?
說(shuō)明：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ǔ)過(guò)程或觸發(fā)器的名稱(chēng)。
ERROR_LINE() 返回導(dǎo)致錯(cuò)誤的例程中的行號(hào)。
ERROR_MESSAGE() 返回錯(cuò)誤消息的完整文本。該文本可包括任何可替換參數(shù)所提供的值，如長(zhǎng)度、對(duì)象名或時(shí)間。
2、有些錯(cuò)誤，如sql語(yǔ)句中的表名稱(chēng)輸入錯(cuò)誤，這是數(shù)據(jù)庫(kù)引擎無(wú)法解析這個(gè)表名稱(chēng)時(shí)，所發(fā)生的錯(cuò)誤在當(dāng)前的try…catch語(yǔ)句中無(wú)法捕獲，必須由外層調(diào)用該存儲(chǔ)過(guò)程的地方使用 ???????????????try…catch來(lái)進(jìn)行捕獲。
?
四、存儲(chǔ)過(guò)程中事務(wù)和try…catch聯(lián)合使用
在存儲(chǔ)過(guò)程中使用事務(wù)時(shí)，如果沒(méi)有try…catch語(yǔ)句，那么當(dāng)set xact_abort on時(shí)，如果有錯(cuò)誤發(fā)生，在批處理語(yǔ)句結(jié)束后，系統(tǒng)會(huì)自動(dòng)回滾所有的sql操作。當(dāng)set xact_abort off時(shí)，如果有錯(cuò)誤發(fā)生，在批處理語(yǔ)句結(jié)束后，系統(tǒng)會(huì)執(zhí)行所有沒(méi)有發(fā)生錯(cuò)誤的語(yǔ)句，發(fā)生錯(cuò)誤的語(yǔ)句將不會(huì)被執(zhí)行。
在存儲(chǔ)過(guò)程中使用事務(wù)時(shí)，如果存在try…catch語(yǔ)句塊，那么當(dāng)捕獲到錯(cuò)誤時(shí)，需要在catch語(yǔ)句塊中手動(dòng)進(jìn)行Rollback操作，否則系統(tǒng)會(huì)給客戶(hù)端傳遞一條錯(cuò)誤信息。如果在存儲(chǔ)過(guò)程開(kāi)始處將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語(yǔ)句塊中就可以根據(jù)xact_state()的值來(lái)判斷是否有事務(wù)處于不可提交狀態(tài)，如果有則可以進(jìn)行rollback操作了。如果在存儲(chǔ)過(guò)程開(kāi)始處將set xact_abort off，那么當(dāng)有錯(cuò)誤發(fā)生時(shí)，系統(tǒng)不會(huì)講xact_state()置為-1，那么我們?cè)赾atch塊中就不可以根據(jù)該函數(shù)值來(lái)判斷是否需要進(jìn)行 rollback了，但是我們可以根據(jù)@@Trancount全局變量來(lái)判斷，如果在catch塊中判斷出@@Trancount數(shù)值大于0，代表還有未提交的事務(wù)，既然進(jìn)入catch語(yǔ)句塊了，那么還存在未提交的事務(wù)，該事務(wù)應(yīng)該是需要rollback的，但是這種方法在某些情況下可能判斷的不準(zhǔn)確。推薦的方法還是將set xact_abort on，然后在catch中判斷xact_state()的值來(lái)判斷是否需要Rollback操作。
下面我們來(lái)看看兩個(gè)例子：
一．使用Set xact_abort???????on
?代碼
Create proc myProcedureAs
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()來(lái)判斷是否有不可提交的事務(wù),不可提交的事務(wù)
--表示在事務(wù)內(nèi)部發(fā)生錯(cuò)誤了。Xact_state()有三種值：-1.事務(wù)不可提交；
--1.事務(wù)可提交；0.表示沒(méi)有事務(wù)，此時(shí)commit或者rollback會(huì)報(bào)錯(cuò)。
if?xact_state()=-1
rollback tran;
end?catch
end
?
?
?
二.使用Set xact_abort off
?
代碼 Create proc myProcedureAs
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來(lái)判斷是否有不可提交的事務(wù)
--只可以使用@@Trancount來(lái)判斷是否有還未提交的事務(wù)，未提交的事務(wù)未必
--就是不可提交的事務(wù)，所以使用@@TranCount>0后就RollBack是不準(zhǔn)確的
if?@@TranCount>0
rollback tran;
end?catch
end
?
?????另外，對(duì)于@@Trancount需要說(shuō)明的是，begin??tran 語(yǔ)句將 @@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
下載文檔
