提供3000多款全球软件/控件产品
针对软件研发的各个阶段提供专业培训与技术咨询
根据客户需求提供定制化的软件开发服务
全球知名设计软件,显著提升设计质量
打造以经营为中心,实现生产过程透明化管理
帮助企业合理产能分配,提高资源利用率
快速打造数字化生产线,实现全流程追溯
生产过程精准追溯,满足企业合规要求
以六西格玛为理论基础,实现产品质量全数字化管理
通过大屏电子看板,实现车间透明化管理
对设备进行全生命周期管理,提高设备综合利用率
实现设备数据的实时采集与监控
利用数字化技术提升油气勘探的效率和成功率
钻井计划优化、实时监控和风险评估
提供业务洞察与决策支持实现数据驱动决策
转帖|其它|编辑:郝浩|2010-11-12 15:09:50.000|阅读 1288 次
概述:表值参数(Table-valued parameters)简称TVP,是SQL Server 2008中引入的一种新特性,它提供了一种内置的方式,让客户端应用可以只通过单独的一条参化数SQL语句,就可以向SQL Server发送多行数据。
# 慧都年终大促·界面/图表报表/文档/IDE等千款热门软控件火热促销中 >>
一.摘要
表值参数(Table-valued parameters)简称TVP,是SQL Server 2008中引入的一种新特性,它提供了一种内置的方式,让客户端应用可以只通过单独的一条参化数SQL语句,就可以向SQL Server发送多行数据。
二.简介
在表值参数出现以前,当需要发送多行数据到SQL Server,我们只能使用一些替代方案来实现:
(1) 使用一连串的独立参数来表示多列和多行数据的值。
使用这一方法,可以被传递的数据总量受限于可用参数的个数。SQL Server的存储过程最多可以使用2100个参数。
在这种方法中,服务端逻辑必须将这些独立的值组合到表变量中,或是临时表中进行处理。
(2) 将多个数据值捆绑到带限定符的字符串或是XML文档中,然后再将文本值传递到一个存储过程或语句中。
这种方式要求存储过程或语句中要有必要的数据结构验证和数据松绑的逻辑。
(3) 为多行数据的修改创建一系列独立的SQL语句。
就像在一个SqlDataAdapter中调用Update方法时产生的那些一样,这些更新可以被独立地或是分组成批地提交到服务器。
不过,尽管成批提交中含有多重语句,但这些语句在服务端都是被分开独立执行的。
(4) 使用bcp实用程序或是使用SqlBulkCopy对象将多行数据载入一个表中。
尽管这一技术效率很高,但它并不支持在服务端执行(注:多行数据仍然无法一次性传给存储过程),除非数据是被载入到临时表或是表变量中。
SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性,我们可以很方便地通过T-SQL语句,或者通过一个应用程序,将一个表作为参数传给函数或存储过程。
(1) 表值参数表示你可以把一个表类型作为参数传递到函数或存储过程里。
(2) 表值参数的功能可以允许你向被声明为T-SQL变量的表中导入数据,然后把该表作为一个参数传递到存储过程或函数中去。
(3) 表值参数的优点在于你可以向存储过程或函数发送多行数据,而无需向以前那样必须声明多个参数或者使用XML参数类型来处理多行数据。
三.描述
计划分三部分描述表值参数的应用。
(1) 在T-SQL中创建和使用TVP
(2) 在ADO.NET中利用DataTable对象,将其作为参数传给存贮过程
(3) 在ADO.NET中利用Collection对象,将其作为参数传给存贮过程
四.第一部分:在T-SQL中创建和使用TVP
1. 表值参数具有两个主要部分:SQL Server 类型以及引用该类型的参数,若要创建和使用表值参数,请执行以下步骤:
(1) 创建表类型并定义表结构。
TVP功能的基础是SQL2008中最新的用户自定义表类型(User-Defined Table Types),简称UDTT,它允许用户将表的定义注册为全局周知类型。
注册之后,这些表类型可以像本地变量一样用于批处理中、以及存储过程的函数体中,也就是UDTT的变量可以作为参数在存储过程和参数化TSQL中使用。
用户自定义表类型的使用有许多限制:
(1) 一个用户自定义表类型不允许用来定义表的列类型,也不能用来定义一个用户自定义结构类型的字段。
(2) 不允许在一个用户自定义表类型上创建一个非聚合索引,除非这个索引是基于此用户自定义表类型创建的主键或唯一约束。
(3) 在用户自定义表类型的定义中,不能指定缺省值。
(4) 一旦创建后,就不允许再对用户自定义表类型的定义进行修改。
(5) 用户自定义函数不能以用户定义表类型中的计算列定义为参数来调用。
(6) 一个用户自定义表类型不允许作为表值型参数来调用用户自定义函数。
例如:
/* Create a user-defined table type */
CREATE TYPE OrderItem$Udt AS TABLE(
OrderId int primary key,
CustomerId int,
OrderedAt datetime
)
GO
(2) 声明具有表类型参数的例程。
CREATE PROCEDURE OrderItem$Insert(
@OrderHeaders AS OrderItem$Udt READONLY,
@OrderDetails AS OrderDetail$Udt READONLY)
AS
BEGIN
-- Bulk insert order header rows from TVP
INSERT INTO [OrderItem]
SELECT *, SYSDATETIME() FROM @OrderHeaders
-- Bulk insert order detail rows from TVP
INSERT INTO [OrderDetail]
SELECT *, SYSDATETIME() FROM @OrderDetails
END
GO
(3) 声明表类型变量,并引用该表类型。
IF OBJECT_ID (N'OrderItem', N'U') IS NOT NULL
DROP TABLE [OrderItem]
GO
CREATE TABLE [OrderItem](
OrderId int NOT NULL primary key,
CustomerId int NOT NULL,
OrderedAt datetime NOT NULL,
CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime()
)
GO
(4) 使用 INSERT 语句填充表变量。
DECLARE @OrderItemUdt dbo.OrderItem$Udt
INSERT INTO @OrderItemUdt
VALUES (1,20,GETDATE()),(2,31,GETDATE()),(100,4,GETDATE()),(201,51,GETDATE())
SELECT * FROM @OrderItemUdt
(5) 创建并填充表变量后,可以将该变量传递给例程。
EXEC dbo.OrderItem$Insert @OrderItemUdt,@OrderDetailUdt
SELECT * FROM dbo.OrderItem
2. 优点
表值参数具有更高的灵活性,在某些情况下,可比临时表或其他传递参数列表的方法提供更好的性能。表值参数具有以下优势:
(1)首次从客户端填充数据时,不获取锁。
(2)提供简单的编程模型。
(3)允许在单个例程中包括复杂的业务逻辑。
(4)减少到服务器的往返。
(5)可以具有不同基数的表结构。
(6)是强类型。
(7)使客户端可以指定排序顺序和唯一键。
3. 限制
表值参数有下面的限制:
(1) SQL Server 不维护表值参数列的统计信息。
(2) 表值参数必须作为输入 READONLY 参数传递到 Transact-SQL 例程。
不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。
***如果想要修改那些已经传入到存储过程或参数化语句中的表值型参数中的数据,只能通过向临时表或表变量中插入数据来实现。
(3) 不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。
表值参数可以在 SELECT INTO 的 FROM 子句中,也可以在 INSERT EXEC 字符串或存储过程中。
4. 作用域
(1) 就像其他参数一样,表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。
(2) 表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。可以在动态 Transact-SQL 语句内声明表值变量,并且可以将这些变量作为表值参数传递到存储过程和函数。
(3) 一般多用于行数小于1000行的数据。
应用比较广泛的是在Browse Master的多行数据作为过滤条件时使用。
利用TVP使得一次插入多项或Select多行变得大为简单。过去,我们使用笨拙的逗号分隔列表或XML,虽其能够胜任,但不是以习惯的对象方式存在,而且存取速度也很慢。
例如:会计系统的选择的多个部门多个科目或多个部所时,利用TVP的方式可以大大提高存取的速度也可提高编程的可读性。
五. 例子
USE AdventureWorks
GO
------------------------
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N'OrderItem$Udt' AND ss.name = N'dbo')
DROP TYPE [dbo].[OrderItem$Udt]
GO
CREATE TYPE OrderItem$Udt AS TABLE(
OrderId int primary key,
CustomerId int,
OrderedAt datetime)
GO
------------------------
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N'OrderDetail$Udt' AND ss.name = N'dbo')
DROP TYPE [dbo].[OrderDetail$Udt]
GO
CREATE TYPE OrderDetail$Udt AS TABLE(
OrderId int,
LineNumber int primary key(OrderId,LineNumber),
ProductId int,
Quantity int,
Price money)
GO
---------------------------
IF OBJECT_ID (N'OrderItem', N'U') IS NOT NULL
DROP TABLE [OrderItem]
GO
CREATE TABLE [OrderItem](
OrderId int NOT NULL primary key,
CustomerId int NOT NULL,
OrderedAt datetime NOT NULL,
CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime()
)
GO
--------------------------
IF OBJECT_ID (N'OrderDetail', N'U') IS NOT NULL
DROP TABLE [OrderDetail]
GO
CREATE TABLE [OrderDetail](
OrderId int NOT NULL,
LineNumber int NOT NULL primary key(OrderId,LineNumber),
ProductId int NOT NULL,
Quantity int NOT NULL,
Price money NOT NULL,
CreatedAt datetime2(0) NOT NULL DEFAULT sysdatetime())
GO
------------------------
IF OBJECT_ID(N'OrderItem$Insert',N'P') IS NOT NULL
DROP PROC OrderItem$Insert
GO
CREATE PROCEDURE OrderItem$Insert(
@OrderHeaders AS OrderItem$Udt READONLY,
@OrderDetails AS OrderDetail$Udt READONLY)
AS
BEGIN
-- Bulk insert order header rows from TVP
INSERT INTO [OrderItem]
SELECT *, SYSDATETIME() FROM @OrderHeaders
-- Bulk insert order detail rows from TVP
INSERT INTO [OrderDetail]
SELECT *, SYSDATETIME() FROM @OrderDetails
END
GO
------------------------
DECLARE @OrderItemUdt dbo.OrderItem$Udt
INSERT INTO @OrderItemUdt
VALUES (1,20,GETDATE()),(2,31,GETDATE()),(100,4,GETDATE()),(201,51,GETDATE())
SELECT * FROM @OrderItemUdt
------------------------
DECLARE @OrderDetailUdt dbo.OrderDetail$Udt
INSERT INTO @OrderDetailUdt
VALUES (1,1,11,111,1111),(1,2,12,121,1212.12),(1,3,13,131,1313.13),
(2,1,21,211,2121),(2,2,22,222,2222.22),(2,3,23,231,2323.23),
(100,1,101,1001,1001.1001),(100,2,102,1002,1002.1002),(100,3,103,1003,1003.1003),
(201,1,2011,2011,201.201),(201,2,2012,2012,2012.2012)
SELECT * FROM @OrderDetailUdt
------------------------
EXEC dbo.OrderItem$Insert @OrderItemUdt,@OrderDetailUdt
SELECT * FROM dbo.OrderItem
SELECT * FROM dbo.OrderDetail
GO
六.其他
下一部分的内容为:Table-values parameter(TVP)系列之二: 在ADO.NET中利用DataTable对象,将其作为参数传给存贮过程
本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn
文章转载自:博客转载面对“数字中国”建设和中国制造2025战略实施的机遇期,中车信息公司紧跟时代的步伐,以“集约化、专业化、标准化、精益化、一体化、平台化”为工作目标,大力推进信息服务、工业软件等核心产品及业务的发展。在慧都3D解决方案的实施下,清软英泰建成了多模型来源的综合轻量化显示平台、实现文件不失真的百倍压缩比、针对模型中的大模型文件,在展示平台上进行流畅展示,提升工作效率,优化了使用体验。
本站的模型资源均免费下载,登录后即可下载。模型仅供学习交流,勿做商业用途。
本站的模型资源均免费下载,登录后即可下载。模型仅供学习交流,勿做商业用途。
本站的模型资源均免费下载,登录后即可下载。模型仅供学习交流,勿做商业用途。
服务电话
重庆/ 023-68661681
华东/ 13452821722
华南/ 18100878085
华北/ 17347785263
客户支持
技术支持咨询服务
服务热线:400-700-1020
邮箱:sales@pclwef.cn
关注我们
地址 : 重庆市九龙坡区火炬大道69号6幢