通过 .NET 创建用户自定义函数UDF(User Define Function)与前述的对象创建方式相同，在类中声明 public static 方法。通过 SqlFunction 属性告知该方法是用作创建用户自定义函数。我们在此简单地通过 .NET Framework 所提供的 Regex 对象，验证输入的数据是否符合先前所定义的Regular Expression，程序范例如程序代码列表11-6：
程序代码列表11-6 通过.NET的Regular Expression 验证用户输入的数据格式
Public Shared Function RxValidator(ByVal intOption As Integer, ByVal strTarget As String, _
ByVal strRx As String) As Boolean
'各 Regular Expression 的定义可以到 http://www.regexlib.com/ 网站查
Dim strValiRx As String = ""
Select Case intOption
Case 0 '用户自行输入 Regular Expression
strValiRx = strRx
Case 1 '验证 Http 格式
strValiRx = "(http|ftp|https):\/\/[\w-_]+(\.[\w-_]+)+([\w\-\.,@?^=%&:/~\+" & _
"#]*[\w\-\@?^=%&/~\+#])?"
Case 2 '验证 EMail 格式
strValiRx = "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$"
Case 3 '验证 ZipCode
strValiRx = "^\s*(\d{5}|(\d{5}-\d{4}))\s*$"
End Select
Dim valid As Boolean
If Not Regex.IsMatch(strTarget, strValiRx) Then
'通过 Regex 验证
valid = False
Else
valid = True
End If
Return valid
End Function
通过 Visual Studio 2005 布署完毕，或通过下列 T-SQL 语法注册 .NET 所编写的用户自定义函数 RxValidator 函数后，打开“对象资源管理器”窗口如图11-18，将可以找到这个验证函数。
CREATE FUNCTION RxValidator(@intOption int,@strTarget nvarchar(4000)
,@strRx nvarchar(4000))
RETURNS bit
EXTERNAL NAME YukonCLR.[YukonCLR.UserDefineFunc].RxValidator
由于我们所编写的函数只返回单一值，所以注册完毕后，是放在“对象资源管理器”窗口的“标量值函数(Scalar-valued Functions)”节点下，如图11-19 所示：
图11-18 AdventureWorks 数据库底下的 RxValidator 函数
分别执行下列三行 T-SQL 语法，将测试数据传入我们自定义的 RxValidator 函数中。第一项的测试会返回False，在“结果”窗口以 0 显示，如图11-19 所示，这表示你所输入的数据格式不是 Http 格式的数据，第二项执行的结果将会返回True(以 1 显示)，你也可以将用来验证的 Regular Expression 语法传入第三个参数，以进行验证第二个参数值是否符合该Regular Expression 的要求，如程序代码列表11-7 中第三项验证方式：
程序代码列表11-7 通过 T-SQL 注册用户自定义函数
-通过 Regular Expression 验证数据格式
SELECT dbo.RxValidator(1,'12345','')
GO
SELECT dbo.RxValidator(1,'http://www.dbworld.com.tw','')
GO
SELECT dbo.RxValidator(0,'aaa@dbworld.com.tw','^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$')
图11-19 通过 T-SQL 验证输入字符串是否符合Regular Expression 格式所返回的结果
接着我们用下列 T-SQL 语法创建数据表 tblTestCheck，利用刚才定义的 RxValidator 自定义函数定义字段检查（Check）的约束（Constraint），只有符合 Http 格式的数据才可以放入到某个数据字段中。
CREATE TABLE tblTestCheck
(
C1 int identity(1,1) primary key,
HttpAddress nvarchar(100) CHECK(dbo.RxValidator(1,HttpAddress,'')=1)
)
建立好 tblTestCheck 数据表后，使用下列语法测试。
INSERT tblTestCheck VALUES('12345')
由于不符合我们所定义 的Http 格式的 Regular Expression 规则，因此会出现下列报错信息而无法加入到数据表中。
信息 547，级别 16，状态 0，行 1
INSERT 表达式与 CHECK 条件约束 "CK__tblTestCh__HttpA__5708E33C" 冲突。冲突发生在数据库 "AdventureWorks"，数据表 "dbo.tblTestCheck", column 'HttpAddress'。
接着我们使用符合 Http 格式的数据，使用下列 T-SQL 语法添加数据，才可以存入数据表内。
INSERT tblTestCheck VALUES('https://www.dbworld2.com.tw')
由于上述语法符合Regular Expression 规则，因此通过 SELECT 语法查询该数据表得到的结果，如下图11-20所示：
图11-20 tblTestCheck数据表内的数据
另外，若你对 Regular Expression 不熟，但希望引用相关验证的公式，可以到以下的网址查询：
http://www.regexlib.com/
除了返回单一值的函数，我们也可以创建返回数据表结果的函数，这称为 Table-Valued Function(TVF)。前述的标量值函数通常搭配数据字段使用，如：
select Schema.纯量函数(字段) from 数据表
而 TVF 则放在数据表的位置，如：
select 字段 from Schema.TVF函数()
通过 .NET CLR 编写的 TVF 函数类必须实现返回 IEnumerable 接口对象的方法，该界面中有迭代操作符(Iterator)，让 SQL Server 只需以类似循环的方式持续调用，并以串流的方式将结果返回前端，通过迭代操作符逐条读出所有记录直到读完为止，就可以取得整个自定义函数所建立的数据表。
而你所编写程序代码访问的数据源并不需要是关系数据库，例如访问 XML 文件或是 Excel 工作表，然后逐条返回 SQL Server 直到文件读取完毕。而前端程序仍以关系数据表的方式处理执行结果，这可以增加访问数据源的弹性。
而 .NET CLR 的 TVF 执行过程中类似用 T-SQL 所编写的 inline Table-valued Function，直接将查询结果所形成的数据表返回到前端应用程序，但不需要如 T-SQL 编写的 Multi-statement Table-valued Function，先创建临时数据表而后逐条填入该临时数据表，因此 .NET CLR 的 TVF 执行起来较有效率。
编写 TVF 与先前返回单一值的函数不同，它需要以类来实现。其内有两个函数，一是 InitMethod 方法，返回含有 IEnumerable 接口的对象实例。另一是通过 FillRowMethodName 属性告知的方法，让 SQL Server 通过迭代操作符重复调用。在该方法中，将迭代操作符所代表的对象解释成多个返回参数，之后 SQL Server 引擎会组织成一条记录，其程序架构见程序代码列表11-8：
程序代码列表11-8 若要创建 Table-valued Function，需要返回实现 IEnumerable 界面
Public Class TabularEventLog
'以 Table 的类型返回 Windows 的 Event Log
'返回一个实现 IEnumerable 界面的对象
Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
Return New EventLog(logname, Environment.MachineName).Entries
End Function
'SQL Engine 通过 IEnumerable 界面所提供的 Enumerator 逐步取出每一个对象
'调用以下的方法，将对象的内容改以输出参数返回，组成单条记录
Public Shared Sub FillRow(ByVal obj As Object, _
Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
message = New SqlChars(eventLogEnTry.Message)
category = New SqlChars(eventLogEnTry.Category)
instanceId = eventLogEnTry.InstanceId
End Sub
End Class
在上述范例中，FillRow 返回的参数需要是仅传出的参数，在 C# 可以用关键字 out 声明，但在 VB.NET 默认并没有这种声明方式，需要用
这是一段有趣的范例程序，通过 EventLog 类返回 Windows 操作系统的事件记录。由于 Entries 属性返回的 System.Diagnostics.EventLogEntryCollection 对象自身就已实现 IEnumerable 接口，所以我们直接返回该对象实例即可。
当 SQL Server 握有该 IEnumerable 接口后，其迭代操作符返回 EventLogEntry 类型实例。SQL Server 会重复调用先前通过属性指定的 FillRow 函数，并传入该迭代操作符所代表的EventLogEntry 类型实例，我们据此读出操作系统内每一条事件记录的相关属性细节。
我们可以通过程序代码列表11-9的语法来注册该 TVF 函数：
程序代码列表11-9 注册 TVF 函数时，字段定义要与函数对应
--将先前注册过的 CLR 对象、Assembley 删除后，重新注册 YukonCLR
CREATE ASSEMBLY YukonCLR FROM
‘C:\BookSamples\SQL 2005 Dev\Ch11_Clr\YukonCLR\bin\YukonCLR.dll'
WITH PERMISSION_SET = UNSAFE
GO
--创建返回 Table 的函数(TVF)
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS
EXTERNAL NAME YukonCLR.[YukonCLR.TabularEventLog].InitMethod
GO
注册完成后使用下列T-SQL语法测试结果。通过上述的函数，你可以轻易获得 Windows 系统的各种 Event Log，如 Application、Security 或 System。
SELECT TOP 100 * FROM dbo.ReadEventLog(N'Application') as T
但要注意在注册组件时要设置权限为 UNSAFE，否则 SQL Server 会不让你访问操作系统的 Event Log。
在以往的 SQL Server 版本，用户自定义数据类型(User Defined Data Type UDT)充其量只能算是替某种既有的 SQL Server 数据类型取个易于辨认的昵称(Alias Name)，例如将 char(10) 定义成 IdNo，而后身份证字段的数据格式都叫做 IdNo。
SQL Server 2005 在核心内容中结合了 .NET CLR 后，可以将以往我们程序设计师所自定义的类 (class)或结构(Structure)当作自定义数据类型，这让程序运行与数据存储之间有更大的弹性，例如我们可以将整个类型的实例(Instance)放入到数据表中，而不像以往一定要取得实例的各个属性，再分别放到数据表的不同字段中。当需要对象实例时，再从各数据表字段取出数据后重组对象。
简单地说就是：用户自定义数据类型提供通过程序集定义特有的数据类型(Data Type)，让数据表内存放的字段数据不局限在 SQL Server 所提供的标准数据类型。
而在访问数据时，大部分的情境下都可以使用自定义数据类型，例如数据表的字段类型是自定义数据类型、存储过程或用户自定义函数间传递的参数可以是自定义数据类型，编写 T-SQL 时声明的变量也可以是自定义数据类型等等。
与前几节介绍的三种对象不同的是，用户自定义数据类型和聚合函数都需要声明public类 (class)或结构(struct)，而用户自定义数据类型是通过 .NET 编写的 SQL Server 内对象最为复杂的一项。由于提供存放对象实例的能力，笔者个人觉得它有可能实现部分面向对象数据库的功能，但微软的白皮书强调勿作这方面的设计，一是误用可能导致性能不佳，二是一些面向对象程序设计的功能不能使用。
用户自定义数据类型须在类中加上 Serializable 特性(Attribute)，并通过 SqlUserDefinedType 属性告知我们自行编写的类或结构是用作用户自定义数据类型。实现该类时，要支持 NULL 的处理、字符串的转换，并提供 public 属性(Property)。且一定实现INullable界面，当编译成程序集并放入到 SQL Server 后，通过 T-SQL 语法 CREATE TYPE 来指定某个类当作自定义类，在此同时 SQL Server 会检验相关的界面是否已实际完成。
上一页 [1] [2] [3] [4] [5] [6] 下一页
责任编辑：cyth
您现在的位置： 