案例情境:在開發實務上有時,需要使用呼叫預存程序A的方式來執行交易,當碰上多筆同時寫入的狀況,可以在設計一個新的預存程序B,來呼叫預存程序A,需考量例外拋回以及資料回復動作。本篇使用一個名為SP_Division的預存程序,來進行除法運算,並將結果寫入DivisionResult資料表,又設計另一個預存程序SP_Multi_Division來進行多筆的除法運算的處理。
資料表新增語法:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DivisionResult](
[result] [decimal](10, 0) NULL
) ON [PRIMARY]
GO
執行單筆交易時,呼叫的預存程序程式碼:
create procedure [dbo].[SP_Division]
@input decimal(10),
@ret_code int output,
@ret_msg varchar(500) output
as
set @ret_code = 0;
set @ret_msg = 'Success';
declare @ret_value int;
begin try
set @ret_value = 0;
--begin transaction;
insert into [dbo].[DivisionResult] values(100/@input);
--commit transaction;
end try
begin catch
if @ret_value = 0
begin
set @ret_code = ERROR_NUMBER();
set @ret_msg = ERROR_MESSAGE();
--rollback transaction;
end
end catch
GO
承上,SP_Division的測試語法如下:
declare @ret_code int
declare @ret_msg varchar(500)
exec SP_Division @input = 100, @ret_code = @ret_code output , @ret_msg = @ret_msg output --正向案例
select @ret_code as '回傳代碼' , @ret_msg as '回傳訊息';
exec SP_Division @input = 0, @ret_code = @ret_code output , @ret_msg = @ret_msg output --反向案例
select @ret_code as '回傳代碼' , @ret_msg as '回傳訊息';
執行結果:
由於上述,兩個測試案例,只有第一項不會產生「發現除以零的錯誤」,因此只會寫入一筆資料至DivisionResult
執行以下觀看DivisionResult資料表的寫入狀況:
select * from [dbo].[DivisionResult]
執行多筆交易時,呼叫的預存程序的程式碼:
CREATE procedure [dbo].[SP_Multi_Division]
@ret_code int output ,
@ret_msg varchar(500) output
as
create table #temp_table
(
value1 decimal(10)
)
insert into #temp_table values (100); --正向測試案例
insert into #temp_table values (5); --正向測試案例
insert into #temp_table values (0); --會產生除以零錯的反向案例
insert into #temp_table values (20); --正向案例
declare @ret_value int;
declare @next_value decimal;
begin try
set @ret_value = 0;
begin transaction
declare division_cursor cursor for (select value1 from #temp_table)
declare @error_count int = 0
open division_cursor
fetch next from division_cursor into @next_value
while(@@FETCH_STATUS = 0)
begin
print '輸入參數:' + convert(varchar, @next_value) ;
exec @ret_value = [dbo].[SP_Division]
@input = @next_value,
@ret_code = @ret_code output,
@ret_msg = @ret_msg output
if(@ret_code = 0)
begin
fetch next from division_cursor into @next_value
end
else
begin
--不要使用return
set @ret_code = -1;
set @ret_msg = '輸入參數:' + convert(varchar, @next_value) + '計算錯誤';
print @ret_msg;
set @error_count = @error_count + 1
break;
end
end
close division_cursor;
deallocate division_cursor;
if @error_count > 0
rollback transaction;
else
commit transaction;
end try
begin catch
if @ret_value = 0
begin
set @ret_code = ERROR_NUMBER();
set @ret_msg = ERROR_MESSAGE();
rollback transaction;
end
end catch
GO
執行測試語法:
declare @ret_code int
declare @ret_msg varchar(500)
exec SP_Multi_Division @ret_code = @ret_code output , @ret_msg = @ret_msg output
執行結果:當cursor將指標移至負向測試案例時,訊息結果並不會顯示「發現除以零的錯誤」,是由於該例外已經在SP_Division中被處理掉,而外層的SP_Multi_Division僅能得知影響的資料數,如下圖。(因此才在程式中使用@ret_code及@ret_msg來做外層的錯誤判斷處理)
執行以下觀看DivisionResult資料表的寫入狀況:
select * from [dbo].[DivisionResult]
因程式碼中有加入RollBack Transaction,因此資料會做回復動作,取消所有測試案例的insert動作。
※補充:
1.內層的預存程序SP_Division可以不需加入begin、commit、rollback transaction,因單筆發生Exception,就不會進行寫入動作
2.不可在Begin Transaction與Commit Transaction中加入Return,否則會發生以下錯誤