[SQL]於預存程序中使用「使用者定義資料表類型」進行多筆資料新增

2019-11-05

3018
0
SQL
2019-11-11

案例情境：當使用者於系統前端操作資料的新增時，資料會由WEB層進入AP層存取資料庫，來完成資料寫入的動作。單筆資料的新增，預存程序端可透過傳統的資料型別來接資料；但當前端資料來源為多筆資料時，傳統的做法會造成AP層至DB端的Connection次數過多，增加AP層存取資料庫的負荷量。因此可使用資料表類型的物件，將多筆資料以資料表的方式傳遞(TVP)給預存程序，來達到一次性的傳輸處理並回傳，減輕伺服器負擔。本範例為一個車輛資料表(CarType)的存取：透過預存程序SP_CAR_TYPE_INFO進行資料新增，其中輸入參數的型態為「使用者定義資料表類型」(DataInfo)，於預存程序中進行逐筆資料的重複性檢查並寫入CarType資料表。

CarType資料表新增指令碼：

CREATE TABLE [dbo].[CarType](
	[CarID] [varchar](10) NOT NULL,
	[CarName] [varchar](30) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[CarID] ASC,
	[CarName] ASC
)WITH (
  PAD_INDEX = OFF, 
  STATISTICS_NORECOMPUTE = OFF, 
  IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY]
) ON [PRIMARY]

GO

使用者定義資料表類型DataInfo新增指令碼：

CREATE TYPE [dbo].[DataInfo] AS TABLE(
	[id] [varchar](10) NULL,
	[name] [varchar](30) NULL
)
GO

預存程序SP_CAR_TYPE_INFO新增指令碼：

CREATE PROCEDURE [dbo].[SP_CAR_TYPE_INFO]
	@pi_info DataInfo readonly,
	@po_ret_code int output,
	@po_ret_msg varchar(100) output
AS
	set @po_ret_code = 0
	set @po_ret_msg = 'Success'
	declare @id varchar(10),
			@name varchar(30),
			@errorCount int = 0
	begin try
	
		declare @info_cursor as cursor
		set @info_cursor = cursor fast_forward for 
		(
			select id, name from @pi_info
		)
		begin transaction
		open @info_cursor
		fetch next from @info_cursor into @id , @name
		while(@@FETCH_STATUS = 0)
		begin
			if (select count(CarID) from CarType where CarID = @id) > 0
			begin
				set @po_ret_code = -1
				set @po_ret_msg = '輸入的資料已存在'
				set @errorCount = @errorCount + 1
				break
			end
			else
			begin
				insert into CarType values
				(
					@id,
					@name
				)
				print 'insert'
			end
			fetch next from @info_cursor into @id , @name
		end
		close @info_cursor
		deallocate @info_cursor
		if(@errorCount = 0)
			commit transaction
		else
			rollback transaction
	end try
	begin catch
		set @po_ret_code = ERROR_NUMBER()
		set @po_ret_msg = ERROR_MESSAGE()
	end catch

GO

預存程序模擬多筆寫入測試語法：

declare @table DataInfo
insert into @table values('TTT-961','CAR1')
insert into @table values('UJN-513','CAR2')
insert into @table values('QRF-478','CAR3')
declare @po_ret_code varchar(10),
		@po_ret_msg varchar(100)
exec SP_CAR_TYPE_INFO
	@pi_info = @table,
	@po_ret_code = @po_ret_code output,
	@po_ret_msg = @po_ret_msg output


select @po_ret_code, @po_ret_msg


select * from [dbo].[CarType]

執行結果：

回首頁