详解 SQL 中的表值参数-低调大师

详解 SQL 中的表值参数

2021-06-28 543

你好，我是悟空。

本文已收录至Github，欢迎 Star：https://github.com/Jackson0714/PassJava-Learning
个人网站：http://www.passjava.cn

一、什么是 TVP?

表值参数 Table-Value Parameter (TVP) 提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式，而不需要多次往返或特殊服务器端逻辑来处理数据。您可以使用表值参数来包装客户端应用程序中的数据行，并使用单个参数化命令将数据发送到服务器。传入的数据行存储在一个表变量中，然后您可以通过使用 Transact-SQL 对该表变量进行操作。

可以使用标准的 Transact-SQL SELECT 语句来访问表值参数中的列值。

简单点说就是当想传递 aaaa,bbbb,cccc,dddd 给存储过程时，可以先将 aaa,bbb,ccc,dddd 存到一张表中：

然后将这张表传递给存储过程。

如：当我们需要查询指定产品的信息时，通常可以传递一串产品 ID 到存储过程里面，如"1,2,3,4"，然后查询出 ID=1 或 ID=2 或 ID=3 或 ID=4 的产品信息。

可以先将"1,2,3,4"存到一张表中，然后将这张表传给存储过程。

这种方法有什么优势呢？请接着往下看。

二、TVP 传递多行

早期版本是怎么在 SQL Server 中传递多行的？在 SQL Server 2008 中引入表值参数之前，用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。开发人员可以选择使用以下选项，将多个行传递给服务器：

使用一系列单个参数表示多个数据列和行中的值。使用此方法传递的数据量受所允许的参数数量的限制。 SQL Server 过程最多可以有 2100 个参数。必须使用服务器端逻辑才能将这些单个值组合到表变量或临时表中以进行处理。

将多个数据值捆绑到分隔字符串或 XML 文档中，然后将这些文本值传递给过程或语句。此过程要求相应的过程或语句包括验证数据结构和取消捆绑值所需的逻辑。

针对影响多个行的数据修改创建一系列的单个 SQL 语句，例如通过调用 SqlDataAdapter 的 Update 方法创建的内容。可将更改单独提交给服务器，也可以将其作为组进行批处理。不过，即使是以包含多个语句的批处理形式提交的，每个语句在服务器上还是会单独执行。

使用 bcp 实用工具程序或 SqlBulkCopy 对象将很多行数据加载到表中。尽管这项技术非常有效，但不支持服务器端处理，除非将数据加载到临时表或表变量中。

三、案例

当我们需要查询指定产品的信息时，通常可以传递一串产品 ID 到存储过程里面，如"1,2,3,4"，然后查询出 ID=1 或 ID=2 或 ID=3 或 ID=4 的产品信息。

我们可以先将“1,2,3,4”存到一张表中，然后作为参数传给存储过程。在存储过程里面操作这个参数。

3.1 使用 TVP 查询产品

查询产品 ID=1,2,3,4,5 的产品

public static void TestGetProductsByIDs()
{
    Collection<int> productIDs = new Collection<int>();
    Console.WriteLine();
    Console.WriteLine("----- Get Product ------");
    Console.WriteLine("Product IDs: 1,2,3,4,5");
    productIDs.Add(1);
    productIDs.Add(2);
    productIDs.Add(3);
    productIDs.Add(4);
    productIDs.Add(5);

    Collection<Product> dtProducts = GetProductsByIDs(productIDs);
    foreach (Product product in dtProducts)
    {
        Console.WriteLine("{0}   {1}", product.ID, product.Name);
    }

}

查询的方法：

/// <summary>
/// Data access layer. Gets products by the collection of the specific product' ID.
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
/// <returns></returns>
public static Collection<Product> GetProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
    Collection<Product> products = new Collection<Product>();
    DataTable dtProductIDs = new DataTable("Product");
    dtProductIDs.Columns.Add("ID", typeof(int));

    foreach (int id in productIDs)
    {
        dtProductIDs.Rows.Add(
            id
        );
    }
    
    SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    //SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procGetProducts", tvpProduct);
    
    using (SqlDataReader dataReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "procGetProductsByProductIDsTVP", tvpProduct))
    {
        while (dataReader.Read())
        {
            Product product = new Product();
            product.ID = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0);
            product.Name = dataReader.IsDBNull(1) ? (string)null : dataReader.GetString(1).Trim();
    
            products.Add(product);
        }
    }
    return products;

}

创建以产品 ID 作为列名的 TVP：

IF NOT EXISTS(  SELECT * FROM sys.types WHERE name = 'ProductIDsTVP')
	CREATE TYPE [dbo].[ProductIDsTVP] AS TABLE
	(
		[ID] INT
	)
GO

查询产品的存储过程：

/****** Object:  StoredProcedure [dbo].[procGetProductsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procGetProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
GO

Create PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
(
	@ProductIDsTVP ProductIDsTVP READONLY
)
AS
            
SELECT p.ID, p.Name
	
FROM Product as p
INNER JOIN @ProductIDsTVP as t on p.ID = t.ID

3.2 使用 TVP 删除产品

删除产品 ID=1,5,6 的产品

public static void TestDeleteProductsByIDs()
{
    Collection<int> productIDs = new Collection<int>();
    Console.WriteLine();
    Console.WriteLine("----- Delete Products ------");
    Console.WriteLine("Product IDs: 1,5,6");
    productIDs.Add(1);
    productIDs.Add(5);
    productIDs.Add(6);
    DeleteProductsByIDs(productIDs);
}

 删除的方法：

/// <summary>
/// Deletes products by the collection of the specific product' ID
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
public static void DeleteProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
    Collection<Product> products = new Collection<Product>();
    DataTable dtProductIDs = new DataTable("Product");
    dtProductIDs.Columns.Add("ID", typeof(int));

    foreach (int id in productIDs)
    {
        dtProductIDs.Rows.Add(
            id
        );
    }
    
    SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procDeleteProductsByProductIDsTVP", tvpProduct);

}

删除产品的存储过程：

/****** Object:  StoredProcedure [dbo].[procDeleteProductsByIDsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procDeleteProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
GO

Create PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
(
	@ProductIDsTVP ProductIDsTVP READONLY
)
AS
            
DELETE p FROM Product AS p
INNER JOIN @ProductIDsTVP AS t on p.ID = t.ID

3.3 使用 TVP 增加产品

增加产品

ID=5,Name=bbb

ID=6,Name=abc

public static void TestInsertProducts()
{
    Collection<Product> products = new Collection<Product>();
    Console.WriteLine();
    Console.WriteLine("----- Insert Products ------");
    Console.WriteLine("Product IDs: 5-bbb,6-abc");
    products.Add(
        new Product()
        {
            ID = 5,
            Name = "qwe"
        });

    products.Add(
        new Product()
        {
            ID = 6,
            Name = "xyz"
        });
    
    InsertProducts(products);

}

增加的方法：

/// <summary>
/// Inserts products by the collection of the specific products.
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void InsertProducts(SqlConnection conn, Collection<Product> products)
{
    DataTable dtProducts = new DataTable("Product");
    dtProducts.Columns.Add("ID", typeof(int));
    dtProducts.Columns.Add("Name", typeof(string));

    foreach (Product product in products)
    {
        dtProducts.Rows.Add(
            product.ID,
            product.Name
        );
    }
    
    SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procInsertProductsByProductTVP", tvpProduct);

}

增加产品的存储过程：

/****** Object:  StoredProcedure [dbo].[procInsertProductsByProductTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procInsertProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procInsertProductsByProductTVP]
GO

Create PROCEDURE [dbo].[procInsertProductsByProductTVP]
(
	@ProductTVP ProductTVP READONLY
)
AS
            
INSERT INTO Product (ID, Name)
SELECT
	t.ID, 
	t.Name
FROM @ProductTVP AS t

GO

3.4 使用 TVP 更新产品

将 ID=2 的产品的 Name 更新为 bbb

将 ID=6 的产品的 Name 更新为 abc

public static void TestUpdateProducts()
{
    Collection<Product> products = new Collection<Product>();
    Console.WriteLine();
    Console.WriteLine("----- Update Products ------");
    Console.WriteLine("Product IDs: 2-bbb,6-abc");
    products.Add(
        new Product()
        {
            ID = 2,
            Name = "bbb"
        });

    products.Add(
        new Product()
        {
            ID = 6,
            Name = "aaa"
        });
    
    UpdateProducts(products);

}

　更新的方法：

/// <summary>
/// Updates products by the collection of the specific products
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void UpdateProducts(SqlConnection conn, Collection<Product> products)
{
    DataTable dtProducts = new DataTable("Product");
    dtProducts.Columns.Add("ID", typeof(int));
    dtProducts.Columns.Add("Name", typeof(string));

    foreach (Product product in products)
    {
        dtProducts.Rows.Add(
            product.ID,
            product.Name
        );
    }
    
    SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procUpdateProductsByProductTVP", tvpProduct);

}

创建以产品 ID 和产品 Name 作为列名的 TVP：

IF NOT EXISTS(  SELECT * FROM sys.types WHERE name = 'ProductTVP')

	CREATE TYPE [dbo].[ProductTVP] AS TABLE(
		[ID] [int] NULL,
		[Name] NVARCHAR(100)
	)

GO

增加产品的存储过程：

/****** Object:  StoredProcedure [dbo].[procUpdateProductsByIDs]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procUpdateProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procUpdateProductsByProductTVP]
GO

Create PROCEDURE [dbo].[procUpdateProductsByProductTVP]
(
	@ProductTVP ProductTVP READONLY
)
AS
            
Update p
SET 
	p.ID = t.ID, 
	p.Name = t.Name
FROM product AS p
INNER JOIN @ProductTVP AS t on p.ID = t.ID

GO

结果：

注意：

（1）无法在表值参数中返回数据。表值参数是只可输入的参数；不支持 OUTPUT 关键字。

（2）表值参数为强类型，其结构会自动进行验证。

（3）表值参数的大小仅受服务器内存的限制。

（4）删除表值参数时，需要先删除引用表值参数的存储过程。

参考资料:

表值参数 https://msdn.microsoft.com/zh-cn/library/bb675163.aspx

表值参数（数据库引擎）https://msdn.microsoft.com/zh-CN/Library/bb510489(SQL.100).aspx

作者简介：悟空，8年一线互联网开发和架构经验，用故事讲解分布式、架构设计、Java 核心技术。《JVM性能优化实战》专栏作者，开源了《Spring Cloud 实战 PassJava》项目，自主开发了一个 PMP 刷题小程序。

我是悟空，努力变强，变身超级赛亚人！

微信关注我们

原文链接：https://my.oschina.net/u/4499317/blog/5113325

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

敏捷史话（十一）：敏捷宣言“间谍”——Steve Mellor

Steve Mellor 是敏捷宣言的签署人之一，他自称是作为“间谍”去参加雪鸟会议的。起初收到会议邀请时，Steve 非常惊讶，因为他所做的工作一直都是关于建模方面的，很少将深受敏捷实践者喜爱的编码和测试作为重点。确实，我们很少会看到“敏捷”和“建模”同时出现，接下来我们就来了解 Steve Mellor 与它们的故事吧。 Steve Mellor 与“敏捷” 在收到会议邀请前，Steve 刚读过 Kent Beck 的《极限编程》，书中所说的：不重视前期思考、憎恶模型、反对文档……这些理论着实吓到了他，不过也激发了他的好奇心。冬日里，闲在落基山脉无事可做，Steve 决定去参加雪鸟会议一探究竟。会议上，Steve 坦言，他原本想邪恶地来阻挠雪鸟会议的计划，但是在会议的过程中，他却发现自己对大家所提出的绝大多数观点都十分赞同。比如，对“前期大规模设计”的过度强调是存在的。就这样，Steve 成为了一名敏捷的支持者，只不过他关注的仍然是建模的价值，尤其是自己十多年来专注于构建的可执行模型。 Steve Mellor 与“建模” 就在雪鸟会议之前，Steve几乎与所有的宣言签署...

2021-06-28

547

前言现在各类项目为了降低项目、服务模块间的高度耦合性，提出了“前后端分离”，而前后端分离的项目该如何打包呢？一般的做法是前端项目打包完，将打包文件手动复制到后端项目工程的src\main\resources\static目录下，再进行后端工程项目打包，这样手动来回复制、多次打包总是让人觉得麻烦。（即使采用Jenkins打包部署，也会存在上面2次打包过程）为了解决上述问题，我特意查询了Maven build的相关配置及插件，发现解决上述问题，通过Maven自动打包整合其实不难，在此与大家进行分享。前后端项目结构要求以Spring Boot + Vue的向后端项目为例说明。通过Maven构建项目，针对子父项目结构创建前端、后端工程，结构如下： spring-boot-vue-parent |---spring-boot # spring boot后端工程 |---src |---main |---java |---... |---resources |---static # 存放前端资源的目录 |---pom.xml # spring-boot后端工程的pom....

2021-06-28

539

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。