SQL Server 2000 用户定义函数(第2部分)

ZDNet软件频道 时间:2002-04-18 作者:BUILDER.COM |  我要评论()
本文关键词:
在本文的前一部分,我们说UDF会返回数值或者数据表。现在让我们进一步了解下如何创建和执行返回数值和表的UDF。
返回表的UDF

除了返回数值以外,UDF还可以返回数据表变量,这是SQL Server 2000中引入的一种新型数据类型。数据表变量类似临时表但经过优化实现了更快的响应速度。UDF会确定变量范围;你只需要在指定的范围以内把变量当作表来处理即可。这类函数可以用做过滤、修改、插入和删除数据。

返回表的UDF有两种形式:内联(inline)和多语句(multistatement)。Inline函数由简单的SELECT语句组成;multistatement函数则包含了多条语句,而且这些语句全都用来创建和处理表变量。这两种形式比标准的视图更具灵活性,因为它们接受参数并且可以直接在T-SQL语句中接受访问。为了对此进行说明,我们创建了一些返回表的UDF,然后在SQL Server 2000随带的Northwind数据库中运行它们。

内联UDF

Inline 函数由单个SELECT语句组成,由其定义函数(结果表)。Inline函数的语法如清单D所示。

inline UDF还有个显著特性:缺乏返回变量(参看表A)。列和数据类型都在定义函数的SELECT语句中表达。清单E 包含了一个inline函数的例子,它接受单一参数返回指定雇员的货物定单。

该函数首先给提示用户输入EmployeeID值。用户响应之后,函数只返回EmployeeID值匹配用户响应的记录,也就是一个参数视图(结果集合不能更新)。为了用T-SQL 语句执行UDF,可以如下方式使用SELECT语句:
SELECT * FROM dbo.fnt_EmpOrders (employee#)

在以上的代码中,employee#表示雇员,图B显示了给dbo.fnt_EmpOrders 赋EmployeeID值为1之后的结果。

图B

可以在FROM字句中运行inline UDF

存储过程中的Inline UDF

Inline函数其实仅仅是一个简单的SELECT语句,数据表的设计全能排上用场。Inline函数还可以用在存储过程中。比如说,我们创建了一个存储过程,dbo.usp_CustOrders,其中包含了JOIN,连接到名为dbo.fnt_CustOrders()的UDF。这个存储过程计算没个顾客的定单总数(usp和fnt前缀分别代表用户存储过程和函数)。清单F 显示了dbo.usp_CustOrders过程, 清单G 显示了我们编写的inline UDF。

你可以用inline函数修改现有的数据,如清单H所示。

Query Analyzer中的以下语句执行了dbo.ftn_CustomerName:
UPDATE dbo.fnt_CustomerName('ALFKI')
Set CompanyName = 'Martin Reid'

你不会从Query Analyzer中看到结果而必须查看Customers表。该函数把CompanyName条目从ALFKI(CustomerID)更新为Martin Reid。UDF自身只返回一条ALFKI的记录。SQL UPDATE语句实际地修改数据。这样做的效率比较高,因为你可以采用dbo.fnt_CustomerName执行各类任务(假如你想实验这种做法,我们建议你运行Northwind表的拷贝或者保证你完成操作之后重新恢复CompanyName条目数据为ALFKI)。

Multistatement

Multistatement(也称做multiline)UDF也会返回表,但是它们采用多个SQL语句,在BEGIN/END块中封闭的每一条语句负责创建表变量和添加数据。Multiline函数获取数据但比inline函数更为灵活,因为你可以控制表的设计。但和inline函数不同的是,RETURNS字句会用列和数据类型设置TABLE类型。如清单I 所示的语法比先前的例子要复杂得多。

清单J 中的例子返回一个表,这个表由来自Orders表的数据组成。此外,该表还包含了Employee、Country和EmployeeID条目。该函数接受3个参数值,两个employee值和一个country值。

你可以如下所示在SELECT语句中执行该函数:
SELECT * FROM dbo.fnt_EmpCountryOrders(4,6,'USA')

返回结果包含了EmployeeID值等于4、5和6而且Country等于USA得记录。

新事物

多年来SQL Server一直因为缺乏类似Oracle和其他竞争产品的灵活性而广受到批评。到今天,MS SQL Server终于支持UDF了。假如你是一个数据库管理员这应该算是个好消息。现在你的工具箱里又加入了一件好工具。

责任编辑:炒饭

百度大联盟认证黄金会员Copyright© 1997- CNET Networks 版权所有。 ZDNet 是CNET Networks公司注册服务商标。
中华人民共和国电信与信息服务业务经营许可证编号:京ICP证010391号 京ICP备09041801号-159
京公网安备:1101082134