[SQL]於預存程序中進行單筆及多筆交易資料的insert案例測試

2019-10-29

4143
0
SQL

案例情境：在開發實務上有時，需要使用呼叫預存程序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，否則會發生以下錯誤

回首頁