在SQL Server中，表值參數(shù)（Table-Valued Parameters, TVPs）提供了一種將多行數(shù)據(jù)作為參數(shù)傳遞給存儲過程或函數(shù)的方法。這種方法在需要處理批量數(shù)據(jù)或復(fù)雜數(shù)據(jù)集時非常有用，因為它比使用多個單個參數(shù)或使用臨時表或表變量更靈活和高效。

1、簡介

適用于：

• SQL Server
• Azure SQL 數(shù)據(jù)庫
• Azure SQL 托管實例

參考官方文檔地址
https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16

表值參數(shù)是使用用戶定義的表類型來聲明的。 使用表值參數(shù)，可以不必創(chuàng)建臨時表或許多參數(shù)，即可向 Transact-SQL 語句或例程（如存儲過程或函數(shù)）發(fā)送多行數(shù)據(jù)。

表值參數(shù)與 OLE DB 和 ODBC 中的參數(shù)數(shù)組類似，但具有更高的靈活性，且與 Transact-SQL 的集成更緊密。 表值參數(shù)的另一個優(yōu)勢是能夠參與基于數(shù)據(jù)集的操作。

Transact-SQL 通過引用向例程傳遞表值參數(shù)，以避免創(chuàng)建輸入數(shù)據(jù)的副本。 可以使用表值參數(shù)創(chuàng)建和執(zhí)行 Transact-SQL 例程，并且可以使用任何托管語言從 Transact-SQL 代碼、托管客戶端以及本機客戶端調(diào)用它們。

2、優(yōu)點

就像其他參數(shù)一樣，表值參數(shù)的作用域也是存儲過程、函數(shù)或動態(tài) Transact-SQL 文本。 同樣，表類型變量也與使用 DECLARE 語句創(chuàng)建的其他任何局部變量一樣具有作用域。 可以在動態(tài) Transact-SQL 語句內(nèi)聲明表值變量，并且可以將這些變量作為表值參數(shù)傳遞到存儲過程和函數(shù)。

表值參數(shù)具有更高的靈活性，在某些情況下，可比臨時表或其他傳遞參數(shù)列表的方法提供更好的性能。 表值參數(shù)具有以下優(yōu)勢：

• 首次從客戶端填充數(shù)據(jù)時，不獲取鎖。
• 提供簡單的編程模型。
• 允許在單個例程中包括復(fù)雜的業(yè)務(wù)邏輯。
• 減少到服務(wù)器的往返。
• 可以具有不同基數(shù)的表結(jié)構(gòu)。
• 是強類型。
• 使客戶端可以指定排序順序和唯一鍵。
• 在用于存儲過程時像臨時表一樣被緩存。 從 SQL Server 2012 (11.x) 及更高版本開始，在參數(shù)化查詢中，表值參數(shù)也將緩存。

3、權(quán)限

要創(chuàng)建用戶定義表類型的實例或使用表值參數(shù)調(diào)用存儲過程，用戶必須對該類型或包含該類型的架構(gòu)或數(shù)據(jù)庫具有 EXECUTE 和 REFERENCES 權(quán)限。

4、限制

表值參數(shù)有下面的限制：

• SQL Server 不維護表值參數(shù)列的統(tǒng)計信息。
• 表值參數(shù)必須作為輸入 READONLY 參數(shù)傳遞到 Transact-SQL 例程。 不能在例程體中對表值參數(shù)執(zhí)行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
• 不能將表值參數(shù)用作 SELECT INTO 或 INSERT EXEC 語句的目標(biāo)。 表值參數(shù)可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存儲過程中。

5、表值參數(shù)與 BULK INSERT 操作

表值參數(shù)的使用方法與其他基于數(shù)據(jù)集的變量的使用方法相似；但是，頻繁使用表值參數(shù)將比大型數(shù)據(jù)集要快。 大容量操作的啟動開銷比表值參數(shù)大，與之相比，表值參數(shù)在插入數(shù)目少于 1000 的行時具有很好的執(zhí)行性能。

重用的表值參數(shù)可從臨時表緩存中受益。 這一表緩存功能可比對等的批量插入操作提供更好的伸縮性。 小型行插入操作可以通過使用參數(shù)列表或批量語句（而不是 BULK INSERT 操作或表值參數(shù)）來獲得小的性能改進。 但是，這些方法在編程上不太方便，并且隨著行的增加，性能會迅速下降。

表值參數(shù)在執(zhí)行性能上與對等的參數(shù)陣列實現(xiàn)相當(dāng)甚至更好。

6、示例

6.1、創(chuàng)建表值類型

首先，你需要定義一個表類型，該類型將用作表值參數(shù)的基礎(chǔ)。這可以通過CREATE TYPE語句完成。

CREATE TYPE dbo.Emp_TableType AS TABLE
(
    ID INT NOT NULL,
    Name NVARCHAR(50),
    Age INT
);

6.2、創(chuàng)建測試表及插入數(shù)據(jù)

CREATE TABLE dbo.emp
(
    ID INT NOT NULL CONSTRAINT PK_emp_id PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
)
INSERT INTO dbo.emp VALUES(1,N'superdb',28),(2,N'sqlserver',20)

SELECT * FROM dbo.emp

ID          Name                                               Age
----------- -------------------------------------------------- -----------
1           superdb                                            28
2           sqlserver                                          20

(2 行受影響)

6.3、創(chuàng)建存儲過程示例

一旦定義了表類型，你就可以在存儲過程或函數(shù)中使用該類型作為表值參數(shù)了

下面的示例使用 Transact-SQL 并展示了如何執(zhí)行以下操作：創(chuàng)建表值參數(shù)類型，聲明變量來引用它，填充參數(shù)列表，然后將值傳遞到數(shù)據(jù)庫中的存儲過程。

CREATE PROCEDURE sp_InsertUpdateEmp
  @TVP P_TableType READONLY
AS
BEGIN
	MERGE INTO dbo.emp AS Target  
	USING @TVP AS Source ON Target.ID = Source.ID  
	WHEN MATCHED THEN  
	  UPDATE SET Target.Name = Source.Name,Target.Age=Source.Age  
	WHEN NOT MATCHED BY TARGET THEN  
	  INSERT (ID, Name,Age) VALUES (Source.ID, Source.Name,Source.Age);
END 

注意，表值參數(shù)在存儲過程中必須是只讀的（READONLY）。

6.4、 調(diào)用存儲過程示例

調(diào)用帶有表值參數(shù)的存儲過程時，你需要傳遞一個與表類型相匹配的數(shù)據(jù)表。這通常通過定義一個表變量并使用INSERT語句填充數(shù)據(jù)來完成，或者使用應(yīng)用程序代碼（如C#或VB.NET）直接構(gòu)造表值參數(shù)。

-- 使用表變量
DECLARE @MyTableVar AS dbo.P_TableType;

INSERT INTO @MyTableVar (ID, Name, Age) VALUES (1, 'Alice', 30);
INSERT INTO @MyTableVar (ID, Name, Age) VALUES (2, 'Bob', 25);
INSERT INTO @MyTableVar (ID, Name, Age) VALUES (3, 'tvp', 34);

EXEC dbo.sp_InsertUpdateEmp @TVP = @MyTableVar;

6.5、檢查驗證表數(shù)據(jù)

SELECT * FROM dbo.emp

ID          Name                                               Age
----------- -------------------------------------------------- -----------
1           Alice                                              30
2           Bob                                                25
3           tvp                                                34

(3 行受影響)

7、 注意事項

• 確保表值類型在調(diào)用存儲過程之前已經(jīng)被創(chuàng)建。
• 表值參數(shù)在存儲過程中必須是只讀的。
• 表值參數(shù)提供了一種靈活且性能優(yōu)化的方式來處理批量數(shù)據(jù)。

通過表值參數(shù)，SQL Server允許開發(fā)人員和數(shù)據(jù)庫管理員以更有效、更安全的方式處理復(fù)雜的數(shù)據(jù)集，這在處理大量數(shù)據(jù)或需要高度定制數(shù)據(jù)輸入的場景中特別有用。

到此這篇關(guān)于SQL Server使用表值參數(shù)的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)SQL 表值參數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論