MEMO-Procedure Transaction: SQL Server vs Oracle

同事詢問關於Procedure內是否會自動包成Transaction的問題,我的認知是SQL要額外下SET XACT_ABORT ON,但Oracle則預設會自動包成Transaction。記憶有些模糊,所以索性做個實驗最準。

分別在SQL, Oracle寫了Procedure,先塞入兩筆資料,第三筆故意產生PK重覆錯誤,觀察資料庫是否有前兩筆資料來判別是否有Transaction保護。實驗證明,原先的認知沒錯,二者行為不同。

SQL Server Procedure要將操作包成Transaction的話,記得要加上BEGIN TRAN / COMMIT TRANSET XACT_ABORT ON! [2010-04-26 更正: 原先測試環境中因開啟Implicit Transaction選項,導致測試結果失真,除加入XACT_ABORT設定外,還需加上BEGIN TRAN、COMMIT TRAN,在此感謝Nipsan指正]

/*** SQL Server Test ***/
--建立有PK的Table
create table jefftable (
    id decimal, 
    constraint pk_jefftable primary key (id)
)
--建立SP, 故意塞入重覆PK
create procedure jeffproc
as 
begin
   /* SQL SP要自成Transaction, 要加這列
    set xact_abort on
   */
   begin tran
   insert into jefftable values (1)
   insert into jefftable values (2)
   insert into jefftable values (1)
   commit tran
end
--執行SP, 會產生錯誤
exec jeffproc
--查Table會看到兩筆
select * from jefftable
--清理實驗廢棄物
drop table jefftable
drop procedure jeffproc
 
/*** ORACLE Test ***/
--建立有PK的Table
create table jefftable (
  id decimal, 
  constraint pk_jefftable primary key (id)
)
--建立SP, 故意塞入重覆PK
create procedure jeffproc
is
begin
  insert into jefftable values (1);
  insert into jefftable values (2);
  insert into jefftable values (1);
end;
--執行SP, 會產生錯誤
declare
begin
jeffproc;
end;
--查Table, 沒有資料
select * from jefftable
--清理實驗廢棄物
drop table jefftable
drop procedure jeffproc
歡迎推文分享:
Published 06 November 2009 10:15 AM 由 Jeffrey
Views: 15,414



意見

# 小熊 said on 21 December, 2009 01:17 AM

謝謝您的分享... ^^

在學習這段語法的過程中,

因為

  /* SQL SP要自成Transaction, 要加這列

      set xact_abort on

  */

中間的set xact_abort on沒有變成和註解一樣的綠色,

一開始還弄不太清楚,

小小建議一下也許可以考慮用"--"來標示... ^_^||

# Jeffrey said on 21 December, 2009 03:10 AM

to 小熊,CSharpFormatter在處理註解標色時不是100%精準,我想下回會格外留意,並謝謝你的指正!

# Nipsan said on 23 April, 2010 08:49 PM

  /* SQL SP要自成Transaction, 要加這列

   set xact_abort on

  */

SQL要成Transaction,必须显式加上 BEGIN TRAN,set xact_abort on 只是控制有错就整个transaction rollback.

e.g.

# Jeffrey said on 26 April, 2010 01:15 AM

to Nipsan, 重複驗證後,發現原先測試疑受SSMS Implicit Transation選項影響,導致結果失真,已更正本文。感謝您的指正!

# 丫包 said on 12 October, 2010 04:18 AM

黑大好~..我將sp加入return code..如下:

create procedure jeffprocis

begin  

begin tran

DECLARE @ReturnMsg AS VARCHAR(10);

insert into jefftable values (1,2,'aa');

if @ERROR > 0 GOTO GORollBack;

insert into jefftable values (2,2,'bb');

if @ERROR > 0 GOTO GORollBack;

insert into jefftable values (1,2,'cc'); --錯誤的,因為"1,2"為複合鍵PK

if @ERROR > 0 GOTO GORollBack;

commit tran

SET @ReturnMsg = 'OK'

GORollBack:

ROLLBACK TRANSACTION

SET @ReturnMsg = 'ERROR'

end;

TABLE的PK是使用二個欄位..執行下述的sp..一直會回傳錯誤訊息..不曉得黑大可以給我一些修改的方向嗎?..謝謝~!!

[sp]

DECLARE @Msg VARCHAR(30);

EXEC jeffprocis @Msg OUTPUT;

PRINT @Msg;

[錯誤訊息]

Violation of PRIMARY KEY constraint 'PK_XXX'. Cannot insert duplicate key in object 'dbo.XXX'.

# 丫包 said on 12 October, 2010 04:19 AM

黑大好~..我將sp加入return code..如下:

create procedure jeffprocis

begin  

begin tran

DECLARE @ReturnMsg AS VARCHAR(10);

insert into jefftable values (1,2,'aa');

if @ERROR > 0 GOTO GORollBack;

insert into jefftable values (2,2,'bb');

if @ERROR > 0 GOTO GORollBack;

insert into jefftable values (1,2,'cc'); --錯誤的,因為"1,2"為複合鍵PK

if @ERROR > 0 GOTO GORollBack;

commit tran

SET @ReturnMsg = 'OK'

GORollBack:

ROLLBACK TRANSACTION

SET @ReturnMsg = 'ERROR'

end;

TABLE的PK是使用二個欄位..執行下述的sp..一直會回傳錯誤訊息..不曉得黑大可以給我一些修改的方向嗎?..謝謝~!!

[sp]

DECLARE @Msg VARCHAR(30);

EXEC jeffprocis @Msg OUTPUT;

PRINT @Msg;

[錯誤訊息]

Violation of PRIMARY KEY constraint 'PK_XXX'. Cannot insert duplicate key in object 'dbo.XXX'.

# 丫包 said on 12 October, 2010 04:24 AM

><..黑大..sorry..我PO了二次~~..>"<..

# 丫包 said on 12 October, 2010 04:38 AM

另外...我解決了啦..><..我在外層判斷了是否有此一筆資料.......誤解了tran的功能..它只是在做交易資料的回復..如果資料有錯..還是得用try..catch來整理..

我不是故意洗版的~~..><..

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<November 2009>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication