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