案例情境:當使用者於系統前端操作資料的新增時,資料會由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]
執行結果: