sqlserver08 数据库系统函数介绍之CLR 表类型

数据库提供的函数很多，我想知道他们的用去，到网上搜索，发现介绍的都不全面，所以打算花一段时间，讲他总结一下，也自己学习一下，有问题希望大家纠正一下，

　 　第一篇主要介绍表值函数：msdn 官方描述：表值函数是返回表的用户定义函数，我的理解是用户在数据库自定义函数，自己封装的一个方法，可以在多个地方调用，从 sql 2005 开始，sql server 允许我们在任意托管语言中定义表值函数来扩展微软数据库提供的表值函数的功能，数据通过 IEnumerable 或 IEnumerator 对象从表值函数中返回（这里的返回值要注意一下：对于表值函数，返回表类型的列不能包含时间戳或者是非 Unicode 字符串数据类型）同时不支持 Not NULL 约束，当这里说道时间戳的时候，有些人也许不理解他有什么用，简单介绍一下：（时间戳：是一种自动公开数据库中生成的唯一的二进制数的数据类型，主要作用 可以记录数据库当前更新行，或者添加行，可以有效防止数据冲突，他是唯一的，而且是有数据库自动生存的，如果要检索当前表的当前时间戳，可以使用配置函数 @@DBTs 查询，他返回的是当前最新的时间戳，时间戳虽然是唯一的，但是不推荐使用它作为主键，虽然主键也是唯一的，但是如果涉及到主外键，使用时间戳作为主键就会 有问题，因为如果修改某一行，他的时间戳也会随之修改）

　　Transact-SQL 和CLR 表值函数之间的差异

Transact-SQL	CLR 表值函数
调用此函数的结果具体化到某个中间表，	表值函数表示一种流替代方法，不要求在单个表中具体化整个结果集，返回 IEnumerabler 对象
由于使用中间表，因此他们可以对于结果支持约束和唯一索引	由于不使用中间表，不能使用唯一索引和约束
使用中间表，需要等待填充整个表，才能返回数据，	采用流模型可以确保在第一行可用之后立即使用结果，而不用填充整个表，

 

 

 

 

 

实现表值函数：

　 　在Microsoft.Net Framework 程序集讲表函数作为累的方法实现，表值函数代码必须实现IEnumerabel 接口，IEnumerable 接口在 .net Framework 中定义。在 .net framework 中表示数组和集合的类型已经实现了 IEnumerable 接口，所以可以方便编写将集合或者数据转换为结果集的表值函数。

表值参数：

　 　  表值参数即传递到某一过程或函数的用户定义表类型，用户定义的表类型不能作为表值参数传递到 sql server 进程中执行的托管存储过程或函数，也不能从这些存储或函数中返回，表值参数是 sql server 2008 中的新参数类型，表值参数是使用用户定义的类型来声明的，使用表值参数，可以不必创建临时表或许多参数，既可以向 T-sql 语句或例程（如存储过程或函数）发送多条数据，可以使用表值参数创建和执行 T-sql 例程，

第一步创建表类型并定义表结构

申明具有表类型参数的例程

声明表类型变量，并引用该表类型

使用 Insert 语句填充表类型

创建并填充表类型后，可以将变量传递给例程

 Demo:

  use MyTest
go

--1
if(OBJECT_ID('tbType') is not null)
drop table tbType
go

create table tbType
(
   name nvarchar(50)
   ,id int
 )
 go
 
 --2
 
 if(OBJECT_ID('pro_insert') is not null)
 drop procedure pro_insert
 go
 
 create procedure pro_insert
 (
    @params tbType readonly
 )
  as
 begin
     insert into my_name(id,name)
     select NEWID(),name from @params
 end
  go
 
  declare @myparams as tbType
 
  insert into @myparams(name,id)
  select 'linhao',1
 
 
  exec pro_insert @myparams

输出参数和表值函数

  通过使用输出参数可以从表值函数返回信息，注意，在实现代码表值函数中的相应参数应将按引用传递用作参数，

  在 T_sql 中定义表值函数， 定义表值函数的语法和定义 T_sql 表值函数的语法类似，但增加了 Exteranl name 子句

 在使用CLR 表值函数，必须先启动 CLR 集成，启动方法：

第一种方式：sp_configure 'clr enabled', 1; reconfigure with override

第二种方式：在SQL Server 2008有一个所谓的“方面”工具里面在服务器节点上，点击右键，选择“方面”，然后可以看到有很多方面的设置，其中有一项就是“外围应用配置器”

 

 

将第二个设置为true 表示为启动了 ＣＬＲ表类型，注意sqlserver 默认是关闭的，不启用：

 

将 第二个ClrIntegrationEnalbed 设置为true

现在使用vs 创建项目

我使用的是vs2010 和数据库sql2008

第一步创建项目：

提示：是Visual C# Sql CLR 数据库项目

这里要注意：版本要选择：

不然部署的时候会包版本不一致，

创建好项目后，解决方案下面会出现类似如下：

 

如果要添加类，存储过程都可以，

 

一切完毕后，注意这里是部署不是发布：

 

 

当左下角出现如图：

 

则表示部署成功：

成功以后打开数据库：在对应的数据库会包含下图：

则表示创建成功：

现在可以在 数据库中直接调用dll 了：

 

Demo;

我们在数据库查看：标量值函数：查看代码：

ALTER FUNCTION [dbo].[Function1]()

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [SqlServer.CLR].[UserDefinedFunctions].[Function1]

 

调用这个标量函数：

SELECT *  FROM dbo.My_Name where [Name] != dbo.Function1()

 

这里要注意。调用函数，前面的dbo.不能去掉，否则报错，暂时没有研究出为什么

标准值函数返回的类型是 基本数据类型

这里面的类型