SQL Server 2008使用自定义表类型
SQL Server 2008使用自定义表类型
本文转载：http://www.cnblogs.com/chenxizhang/archive/2009/04/28/1445234.html
在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。有关如何定义表结构的详细信息，请参阅 CREATE TABLE (Transact-SQL)。
若要创建用户定义表类型，请使用 CREATE TYPE 语句。为了确保用户定义表类型的数据满足特定要求，您可以对用户定义表类型创建唯一约束和主键。
有关与用户定义类型相关联的目录视图的信息，请参阅 sys.types 和 sys.table_types。
限制
用户定义表类型具有下列限制：
- 用户定义表类型不能用作表中的列或结构化用户定义类型中的字段。
- 基于用户定义表类型的别名类型
- [NOT FOR REPLICATION] 选项是不允许的。
- CHECK 约束要求保留计算列。
- 计算列的主键必须是 PERSISTED 和 NOT NULL。
- 无法对用户定义表类型创建非聚集索引，除非该索引是对用户定义表类型创建 PRIMARY KEY 或 UNIQUE 约束的结果。（SQL Server 使用索引强制实施任何 UNIQUE 或 PRIMARY KEY 约束。）
- 不能在用户定义表类型的定义中指定 DEFAULT 值。
- 在创建用户定义表类型定义后不能对其进行修改。
- 不能在用户定义表类型的计算列的定义中调用用户定义函数。
安全性
用户定义表类型的权限通过使用下列 Transact-SQL 关键字来遵循 SQL Server 的对象安全模式：CREATE、GRANT、DENY、ALTER、CONTROL、TAKE OWNERSHIP、REFERENCES、EXECUTE、VIEW DEFINITION 和 REVOKE。
下面我用一个实例来讲解一下
-- ================================ -- 创建和使用自定义表类型 -- 陈希章 -- ================================
USE master GO -- ================================ -- 创建测试数据库 -- ================================ CREATE DATABASE demo GO
-- ================================ -- 创建一个表 -- ================================ USE demo GO
CREATE TABLE Customers ( Id int NOT NULL, Name char(10) NULL, PRIMARY KEY (Id) ) GO
USE demo GO -- ================================ -- 创建自定义表类型 -- ================================ CREATE TYPE dbo.CustomerTable AS TABLE ( Id int NOT NULL, Name char(10) NULL, PRIMARY KEY (Id) ) GO
-- ================================= -- 直接使用自定义表类型 -- ================================= DECLARE @c CustomerTable INSERT INTO @c VALUES(1,'Xizhang') SELECT * FROM @c
-- ================================= -- 在存储过程中使用自定义表类型 -- ================================= CREATE PROC GetCustomers (@c CustomerTable READONLY) AS INSERT Customers SELECT * FROM @c --将传过来的参数（其实是一个表）的数据插入到Customers表里面去
-- ================================= -- 调用该存储过程，一次性插入4行数据 -- ================================= DECLARE @temp CustomerTable INSERT INTO @temp VALUES(7,'Xizhang') INSERT INTO @temp VALUES(2,'Xizhang') INSERT INTO @temp VALUES(3,'Xizhang') INSERT INTO @temp VALUES(4,'Xizhang') EXEC GetCustomers @c=@temp SELECT * FROM Customers
-- ================================= -- 清理数据库 -- ================================= USE master GO
DROP DATABASE demo GO
看起来不错对吧，但是你应该马上想到一个问题，如果说这个存储过程要在客户端代码中调用，那么该怎么提供这个参数值呢？
using System.Data.SqlClient;
using System.Data;
class Program
{
static void Main(string[] args)
{
DataTable tb = GetData();
using (SqlConnection conn = new SqlConnection("server=sql2008;database=demo;integrated security=true"))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "GetCustomers";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@c", SqlDbType.Structured);//这个类型很关键
param.Value = tb;
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
Console.WriteLine("完成操作");
Console.Read();
}
private static DataTable GetData()
{
DataTable tb = new DataTable();
tb.Columns.Add("Id",typeof(int));
tb.Columns.Add("Name", typeof(string));
//添加100个客户资料
for (int i = 0; i < 100; i++)
{
DataRow row = tb.NewRow();
row[0] = i;
row[1] = "Name " + i.ToString();
tb.Rows.Add(row);
}
return tb;
}
} 读者可能会疑惑，这样做实在是太棒了，可以一次性写入100行数据呢？我们再来看看在服务端到底发生了什么事情
实际上，在服务端确实会有一个定义临时变量的过程，然后把所有的数据插入到这个变量中去，然后再执行存储过程的
最后，我们可以再深入探讨探讨
1. 客户端是否一定用DataTable类型
-- 推荐使用DataTable类型，但也可以使用其他的类型，例如DataReader的数据流
2. DataTable的字段名称是否要匹配
--不一定。只要顺序一致，类型一样就可以了。
有一个参考的blog，请看下面的链接