(《SQL Server 编程入门经典》 第13章)
用户自定义函数和存储过程非常相似,但它们也有一些行为和能力的区别。
13.1 UDF的定义
用户自定义函数是有序的T-SQL语句集合,该语句集合能够预先优化和编译,并且可以作为一个单元来调用。它和存储过程的主要区别在于返回结果的方式。为了能支持多种不同的返回值,UDF比存储过程有更多地限制。
可以在使用存储过程的时候传入参数,也可以以参数的形式得到返回值。存储过程可以返回值,不过该值是为了指示成功或失败的,而非返回数据。
然而,可以在使用UDF的时候传入参数,但是可以不传出任何值。UDF还可以返回标量(scalar)值,这个值可以是大部分SQL Server的数据类型。UDF还可以返回表。
按照返回值的类型,UDF有两种类型:
返回标量的UDF 返回表的UDF
创建UDF的基本语法:
CREATE FUNCTION [<schema name>.]<function name>
([<@parameer name> [AS] [<schema name>.]<scalar data type>
[= <default value>]
[, ...n]])
RETURNS {<scalar type>|TABLE [(<table definition>)]}
[WITH [ENCRYPTION]|[SCHEMABINDING]|
[RETURNS NOLL ON NULL INPUT|CALLED ON NULL INPUT]|
[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]]
[AS] {EXTERNAL NAME <external method name>
BEGIN
[<function statements>]
{RETURN <type as defined in RESURNS clause>|RETURN
(<select statement>)}
END }[;]
这里一个很大的问题在于是返回一个标量数据类型还是表,是使用基于T-SQL的函数还是使用CLR和.NET。
13.2 返回标量值得UDF
这种类型的UDF和大多数SQL Server内建的函数一样,会向调用脚本或存储过程返回标量值,例如GETDATE()和USER()函数就会返回标量值。
UDF可以返回除了BLOB、CURSOR和TIMESTAMP以外的任何SQL Server中有效的数据类型(包含用户自定义类型)。如果想返回整数,UDF也和存储过程不同的是:
UDF返回值的目的是提供有意义的数据,而不是说明成功或失败。 在查询中可以内联地执行函数,而使用存储过程则不行。
示例——返回去掉时分秒的日期:
CREATE FUNCTION DayOnly(@Date DATETIME)
RETURNS VARCHAR(12)
AS
BEGIN
RETURN CONVERT(VARCHAR(12), @Date, 101)
END
函数的使用方法如下:
SELECT *
FROM Orders
WHERE DayOnly(OrderDate) = DayOnly(GETDATE())
在一个UDF中调用另一个UDF:
CREATE FUNCTION AveragePrice()
RETURNS MONEY
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT AVG(Price) FROM Titles)
END
GO
CREATE FUNCTION PriceDifference(@Price MONEY)
RETURN MONEY
AS
BEGIN
RETURN @Price – AveragePrice()
END
使用UDF可以大大增加查询语句的可读性,并实现了代码重用:
USE pubs
SELECT Title,
Price,
AveragePrice() AS Average,
PriceDifference(Price) AS Difference
FROM Titles
WHERE Type = 'popular_comp'
13.3 返回表的UDF
可以对UDF返回的表执行JOIN,甚至对结果应用WHERE条件。相对简单的函数示例如下:
USE pubs
GO
CREATE FUNCTION fnAuthorList()
RETURN TABLE
AS
RETURN (
SELECT au_id,
au_lname + ', ' + au_fname AS au_name
address AS address1,
city + ', ' + state + ', ' + zip AS address2
FROM authors
)
GO
这样的话,使用这个函数就像使用表一样:
SELECT *
FROM fnAuthorList()
使用返回表的UDF比使用视图的好处在于可以在UDF中将条件参数化,而视图不得不包含不想要的数据,然后再通过WHERE子句过滤。例如:
CREATE VIEW vSalesCount
AS
SELECT au.au_id,
au.aulname + ', ' + au.au_fname AS au_name,
au.address AS address1,
city + ', ' + state + ', ' + zip AS address2,
SUM(s.qty) AS SalesCount
FROM authors au
INNER JOIN titleauthor ta
ON au.au_id = ta.au_id
INNER JOIN sales s
ON ta.title_id = s.title_id
GROUP BY au.au_id,
au.au_lname + ', ' + au.au_fname,
au.address,
au.city + ', ' + au.state + ', ' + zip
GO
SELECT au_name, address1, address2
FROM vSalesCount
WHERE SalesCount > 25
为了使得代码更简单,把所有的代码都封装到一个函数中:
USE pubs
GO
CREATE FUNCTION fnSalesCount(@SalesQty BIGINT)
RETURNS TABLE
AS
RETURN (
SELECT au.au_id,
au.aulname + ', ' + au.au_fname AS au_name,
au.address AS address1,
city + ', ' + state + ', ' + zip AS address2,
SUM(s.qty) AS SalesCount
FROM authors au
INNER JOIN titleauthor ta
ON au.au_id = ta.au_id
INNER JOIN sales s
ON ta.title_id = s.title_id
GROUP BY au.au_id,
au.au_lname + ', ' + au.au_fname,
au.address,
au.city + ', ' + au.state + ', ' + zip
HAVING SUM(qty) > @SalesQty
)
GO
为了执行该函数,只需要调用它并提供参数:
SELECT *
FROM fnSalesCount(25)
再进一步,如果需要查询每一个销售超过25本书以上的作者和出版社的信息,这需要连接UDF返回的表:
SELECT DISTINCT p.pub_name, a.au_name
FROM dbo.fnSalesCount(25) AS a
INNER JOIN titleauthor AS ta
ON a.au_id = ta.au_id
INNER JOIN titles AS t
ON ta.title_id = t.title_id
INNER JOIN publishers AS p
ON t.pub_id = p.pub_id
这里对函数进行了连接,就好像它是表或视图一样。唯一的区别在于可以对它进行参数化。
再进一步,UDF也可以递归调用,并同样存在最深32层的限制。例如,现在需要查询向Andrew Fuller报告的报告链的所有人员:
CREATE FUNCTION dbo.fnGetReports(@EmployeeID AS INT)
RETURNS @Reports TABLE (
EmployeeID INT NOT NULL,
ReportsToID INT NULL
)
AS
BEGIN
DECLARE @Employee AS INT
INERT INTO @Reports
SELECT EmployeeID, ReportsTo
FROM Employees
WHERE EmployeeID = @EmployeeID
SELECT @Employee = MIN(EmployeeID)
FROM Employees
WHERE ReportsTo = @EmployeeID
WHILE @Employee IS NOT NULL
BEGIN
INSERT INTO @Reports
SELECT *
FROM fnGetReports(@Employee)
SELECT @Employee = MIN(EmployeeID)
FROM Employees
WHERE EmployeeID > @Employee AND ReportsTo = @EmployeeID
END
RETURN
END
GO
现在如果需要查询员工ID为2的员工的报告链:
SELECT * FROM fnGetReports(2)
理解决定性
如果SQL Server需要建立一个索引的话,则它必须能决定性地定义(明确定义)对哪个项目进行索引。用户自定义函数可以是决定性的也可以是非决定性的。决定性并不是根据任何参数类型来定义的,而是根据函数的功能来定义的。如果给定了一个特有的有效输入集的话,那么每次函数就都能返回相同的结果,我们就说该函数是决定性的。内建函数是决定性的一个示例是SUM()。3、5、10的总和永远是18。但是GETDATE()的值就是非决定性的,因为每次调用它的时候它都会改变。
为了达到决定性的要求,函数必须满足4个标准:
函数必须是限于模式的。这意味着函数所依赖的任何对象会有一个依赖记录,并且在没有删除这个依赖的函数之前都不允许改变这些对象。 函数引用到的所有其他函数,都必须是决定性的。 不能引用定义在函数本身之外的表(可以使用表变量和临时表)。 不能在函数内部使用扩展的存储过程。
决定性的重要性在于它揭示了是否要在视图或计算列上建立索引。这意味着,如果视图或计算列引用非决定性函数的话,则在该视图或列上将不允许建立任何索引。
可以利用OBJECTPROPERTY函数检查一个函数是不是决定性的——它检验存储在对象的IsDeterministic属性来得到结论。例如,我们可以检验DayOnly函数的决定性:
SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'), 'IsDeterministic')
给DayOnly()函数添加上WITH SCHEMABINDING选项后,该函数将是决定性的:
ALTER FUNCTION DayOnly(@Date DATETIME)
RETURNS VARCHAR(12)
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(VARCHAR(12), @Date, 101)
END