SQL Server 2008 表值类型参数

【 tulaoshi.com - 编程语言 】

　　SQL Server 2008 表值类型参数

　　目录

　　准备工作1

　　练习：处理表值类型参数2

　　准备工作

　　预计完成本实验所需的时间

　　40 分钟

　　目标

　　在完成本实验后，您将可以：

　　处理SQL Server 2008当中的表值类型参数。

　　先决条件

　　在完成本实验前，您必须具有：

　　编写Transact-SQL 脚本与使用SQL Server Management Studio的相关经验。

　　实验场景

　　在许多客户场景中，都需要将一个表结构的值（一些数据行）传递到服务器中的一个存储过程/函数当中。这些值可以用来直接更新一个数据表，或根据业务逻辑进行更为复杂的处理操作。表值类型参数提供了一个更为简单的方式来定义一个数据类型，并且允许应用程序创建、更新并向存储过程和函数中传递表值类型的参数。

　　随着在SQL Server 2008中引入的MERGE语句，开发人员可以更为高效的处理常见的数据仓库场景，如检查一个数据行是否存在，然后进行插入或更新。

　　虚拟机环境

　　从开始菜单或桌面上启用Microsoft Virtual PC 。如果Virtual PC 控制台没有启用，请查看系统托盘，然后双击系统托盘当中的Microsoft Virtual PC 。

　　选择Sql08 然后点击Start。

　　在虚拟机运行起来后，可以通过点击右Alt+Del 来向虚拟机发送一个Ctrl+Alt+Del 命令。

　　在登录窗口中，输入以下信息：

　　User name: administrator

　　Password: password01!

　　练习：处理表值类型参数

　　表值类型参数是SQL Server 2008当中的一个新的数据类型。表值类型参数可以使用一些用户定义的表类型来声明。您可以使用表值类型参数向一个T－SQL语句、存储过程或函数中发送多行数据，并且无需创建临时表或创建多个参数。

　　表值类型参数就像在OLE DB和ODBC中的参数数组一样，但是它可以提供更多的灵活性，并与T－SQL更加集成。表值类型参数也可以更加方便的进行基于集合的操作。

　　在本练习中，您的目标是使用单个存储过程，使用多个数据行插入一整个数据集合。

　　在SQL Server 2008以前，没有任何集成的功能，可以支持调用存储过程来完成类似的目标。

　　您将实现一个存储过程，并使用表值类型参数完成这个功能。

　　启动 SQL Server Management Studio

　　点击Start | All Programs | Microsoft SQL Server 2008 | SQL Management Studio　，启动SQL Server Management Studio。

　　在Connect to Server对话框中输入下列信息，然后点击 Connect 按钮：

Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication

　　点击File | Open | File。

　　打开C:SQLHOLS 文件夹，然后打开Table Valued Parameters目录当中的Labscript.sql 脚本文件。

　　使用存储过程中的多个参数插入数据

　　查看并选中 下列代码并点击Execute：

USE SQL2008DEMO
GO
CREATE TABLE dbo.Employee(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
CREATE PROCEDURE NewEmployeeMS(@EmpID int,@EmpName nvarchar(100),@EmpEmail nvarchar(100))
As
BEGIN
INSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)
END

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
execute NewEmployeeMS 1,'John McLean','JohnMcLean@contoso.com'
execute NewEmployeeMS 2,'Bob Smith','BobSmith@contoso.com'
execute NewEmployeeMS 3,'Ted Connery','TedConnery@contoso.com'

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
select * from dbo.Employee;
GO

　　注意: 以上解决方案的缺点在于：

　　1.使用了太多的与服务器的往返行程

　　2.存储过程需要多次重复执行

　　3.低效的代码执行

　　使用本地临时表插入数据

　　查看并选中 下列代码并点击Execute：

USE SQL2008DEMO
GO
Truncate table dbo.Employee

查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
CREATE PROCEDURE NewEmployeeTempTable
As
BEGIN
　INSERT INTO dbo.Employee
　SELECT * FROM #EmployeeTempTable
ENDINSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)
END

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
CREATE TABLE dbo.#EmployeeTempTable(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)
Go

　　注意: 这种临时表是在运行在客户端进行创建，这将会导致在客户端没有定义的情况下，服务器端的存储过程将会执行失败。

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
INSERT INTO #EmployeeTempTable
VALUES(1,'John McLean','JohnMcLean@contoso.com')
INSERT INTO #EmployeeTempTable
VALUES(2,'Bob Smith','BobSmith@contoso.com')
INSERT INTO #EmployeeTempTable
VALUES(3,'Ted Connery','TedConnery@contoso.com')

　　注意: 插入的行在客户端的临时表中产生。

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
exec dbo.NewEmployeeTempTable

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
SELECT * FROM dbo.Employee
GO

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
Drop table dbo.#EmployeeTempTable
GO

　　注意:

　　1.临时表是在磁盘上创建并操作的，这将会导致大量的I/O操作。

　　2.它们在tempdb数据库中进行创建，并非常会导致锁定与阻碍问题。

　　3.在使用完成后，您必需手动清理数据，并删除临时表。

　　4.临时表的使用将会导致频繁的存储过程的重新编译。

　　使用表值类型参数插入数据

　　查看并选中 下列代码并点击Execute：

USE SQL2008DEMO
GO
Truncate table dbo.Employee

　　查看并选中 下列代码并点击Execute：

USE SQL2008DEMO
GO
CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))

　　注意: 在这里需要创建一个表类型来处理表值类型参数。

　　查看并选中 下列代码并点击Execute：

USE SQL2008DEMO
GO
CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
　INSERT INTO dbo.Employee
　SELECT * FROM @EmployeeDetails
END

　　注意: 表值类型参数必须作为输入类型的只读（READONLY）参数传递到过程当中。在过程体当中，您不能在表值类型参数上执行DML操作，如UPDATE, DELETE, 或INSERT操作。

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
DECLARE @NewEmployees EmployeeTableType
INSERT INTO @NewEmployees
VALUES(1,'John McLean','JohnMcLean@contoso.com')
INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','BobSmith@contoso.com')
INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','TedConnery@contoso.com')
EXECUTE NewEmployee @NewEmployees
Go

　　注意: 在过程操作完成后，表值类型参数将不会再存在。

　　查看并选中 下列代码并点击Execute：

USE SQL2008Demo
GO
select * from dbo.Employee

　　注意: 使用表值类型参数的优势

　　表值类型参数提供了更好的灵活性，并且在许多情况下，可以提供比临时表或其它传递列表类型类型的方式更好的性能。表值类型参数提供了下列一些优势：

　　拥有一个良好定义的范围，在这个执行范围结束后，它将会自动清除。

　　不需要在客户端锁定正在处理的数据。

　　不会导致语句的重新编译。

　　提供了一个非常简单的编程模型。

　　可以让您在一个单一的过程当中引入复杂的业务逻辑。

　　减少与服务器的返回行程。

　　可以拥有不同粒度的表结构。

　　是一个强类型的类型定义。

　　允许客户端指定排序顺序和唯一键。

　　关闭所有应用程序并不要保存所有更改。

　　关闭Virtual PC 并不要保存更改。