用户自定义函数

川长思鸟来 2022-09-25 01:24 199阅读 0赞

UDF的定义：

和储存过程类似，用户自定义函数是一组有序的T-SQL语句，这些语句被预先优化和编译，并却可以作为一个单元来调用。UDF和储存过程的主要区别在于结果的返回方式。为了支持多种不同的返回值，UDF比储存过程有更多的限制。使用储存过程时可传入参数值。可以返回值，不过该值用于指示成功或失败，而非返回数据。也可以返回结果集，但是在没有将结果集插入到某种表中以共后面的使用情况下，不能再查询中真正使用他们，使用UDF时可传入参数，但不可传出参数。输出参数的概念被更改为更为健壮的返回值取代了。和系统函数一样，可以返回标量值，这个值的好处是她并不像在储存过程中那样只限于整形数据类型，而是可以返回大多数的SQL Server数据类型。

UDF有以下两种类型：

返回标量值的UDF

返回表的UDF

返回标量值的UDF：

于储存过程不同，用户自定义函数返回值的目的是提供有意义的数据；而对于储存过程来说，返回值只是说明成功或失败，如果失败，则会提供一些关于失败信息的特定信息。

可在查询中内联执行函数（例如，作为SELECT语句的一部分包含他），而使用储存过程则不行。

创建标量UDF:

例：

CREATE FUNCTION dbo.DayOnly(@Date date)
    RETURNS date
    AS
    BEGIN
       RETURN @Date;
    END
    
    GO

把计算平均值和差值的功能封装到两个函数中：

CREATE FUNCTION dbo.AveragePrice()
    RETURNS money
    WITH SCHEMABINDING
    AS 
    BEGIN
       RETURN (SELECT AVG(ListPrice) FROM Production.Product);
    END
    GO
    
    CREATE FUNCTION dbo.PriceDifference(@Price money)
    RETURNS money
    AS 
    BEGIN
       RETURN @Price - dbo.AveragePrice();
    END
    GO

用新的函数而非原有的子查询模型运行查询：

SELECT Name, 
           ListPrice,
           dbo.AveragePrice() AS Average, 
           dbo.PriceDifference(ListPrice) AS Difference
    FROM Production.Product
    WHERE ProductSubcategoryID = 1; -- The Mountain Bikes Sub-cat

返回表的UDF：

内联UDF：

创建一个相对简单的函数：

CREATE FUNCTION dbo.fnContactList()
    RETURNS TABLE
    AS
    RETURN (SELECT BusinessEntityID,
                LastName + ', ' + FirstName AS Name
            FROM Person.Person);
    GO

像使用表一样使用函数：

SELECT *
    FROM dbo.fnContactList();

视图：

CREATE FUNCTION dbo.fnContactSearch(@LastName nvarchar(50))
    RETURNS TABLE
    AS
    RETURN (SELECT p.BusinessEntityID,
                LastName + ', ' + FirstName AS Name,
                ea.EmailAddress
            FROM Person.Person as p
            LEFT OUTER JOIN Person.EmailAddress ea 
              ON ea.BusinessEntityID = p.BusinessEntityID
            WHERE LastName Like @LastName + '%');
    GO
    
    
    SELECT * 
    FROM fnContactSearch('Ad');

检验一个函数的确定性：

SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'), 'IsDeterministic');