SQL Server 2008使用自定义表类型

本文转载:http://www.cnblogs.com/chenxizhang/archive/2009/04/28/1445234.html

在 SQL Server 2008 中,用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数,或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。有关如何定义表结构的详细信息,请参阅 CREATE TABLE (Transact-SQL)

若要创建用户定义表类型,请使用 CREATE TYPE 语句。为了确保用户定义表类型的数据满足特定要求,您可以对用户定义表类型创建唯一约束和主键。

有关与用户定义类型相关联的目录视图的信息,请参阅 sys.typessys.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,请看下面的链接

http://msdn.microsoft.com/zh-cn/library/bb675163.aspx

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏栗霖积跬步之旅

第八章:用通配符进行过滤

表名:products  字段:product_id、product_name、product_price、vend_id(供应商) 1.LIKE操作符: 通配...

1985
来自专栏逸鹏说道

SQL Server 索引和表体系结构(聚集索引+非聚集索引)

聚集索引 概述 关于索引和表体系结构的概念一直都是讨论比较多的话题,其中表的各种存储形式是讨论的重点,在各个网站上面也有很多关于这方面写的不错的文章,我写这...

3469
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle存储过程

Oracle存储过程 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数...

4365
来自专栏hrscy

SQLite 基础

第1页:limit 0, 5 第2页:limit 5, 5 第3页:limit 10, 5 … 第n页:limit 5*(n-1), 5

1864
来自专栏LanceToBigData

MySQL(九)之数据表的查询详解(SELECT语法)二

上一篇讲了比较简单的单表查询以及MySQL的组函数,这一篇给大家分享一点比较难得知识了,关于多表查询,子查询,左连接,外连接等等。希望大家能都得到帮助! 在开始...

22810
来自专栏信安之路

sqlmap自带的tamper你了解多少?

sqlmap 是一款注入神器广为人知,里面的 tamper 常常用来绕过 WAF ,很实用的模块,但是却常常被新手忽略(比如我),今天就整理总结一下 tampe...

1110
来自专栏琯琯博客

Yii2 学习笔记之数据库篇

4517
来自专栏软件开发

MyBatis学习总结(三)——多表关联查询与动态SQL

假定一个员工(emp)拥有一个登录用户(user),员工与用户表之间是一对一关系:

3.9K3
来自专栏lgp20151222

java 调用mysql存储过程

由于本人的码云太多太乱了,于是决定一个一个的整合到一个springboot项目里面。

2352
来自专栏JavaEdge

2018-08-24

函数可以嵌入在sql中使用的,可以在select中调用 存储过程要让sql的query 可以执行, 需要把 mysql_real_connect 的最后一个参...

632

扫码关注云+社区

领取腾讯云代金券