SQLServer中UDF(用户自定义函数)

自定义函数，相当于我们在C#里面写的方法，传递参数，返回你需要的数据!

自定义函数与存储过程不同， 存储过程只返回整数, 失败(0)或者成功(1+),而自定义函数可以返回标量，也可以返回表!自定义函数与视图不同，视图不能传递参数，视图不能返回标量! 而自定义函数能更好的复用代码!

--创建UDF dbo.DayOnly 在数据库-->可编程性-->函数-->标量值函数|表值函数 可以看到创建的UDF
CREATE FUNCTION DayOnly ( @data DATETIME )
RETURNS VARCHAR(12)
AS 
    BEGIN
        RETURN CONVERT(VARCHAR(12),@data,101)
    END    
GO
--调用UDF ,就像调用方法一样 [传递参数,得到返回值]

PRINT dbo.DayOnly(GETDATE()) 

--复杂点的UDF示例

USE pubs
go 

--  返回均价的UDF
CREATE FUNCTION dbo.AveragePrice ( )
RETURNS MONEY
    WITH SCHEMABINDING
AS 
    BEGIN
        RETURN (SELECT AVG(price) FROM dbo.titles)
    END
GO 
-- UDF 中调用另一个 UDF 
CREATE FUNCTION dbo.PriceDiff ( @price MONEY )
RETURNS MONEY
AS 
    BEGIN
        RETURN @price - dbo.AveragePrice() ;
    END
GO

--实际使用…
SELECT  title ,
        price ,
        dbo.AveragePrice() AS '均价' ,
        dbo.PriceDiff(price) AS '差价'
FROM    dbo.titles
WHERE   type = 'popular_comp'
go 
   
   
--返回值为表的 UDF
USE pubs
GO

CREATE FUNCTION dbo.fnAuthorList ( )
RETURNS TABLE --table关键字
AS 
        RETURN
    ( SELECT TOP 10
                au_id ,
                au_lname + ' ' + au_fname AS [NAME]
      FROM      dbo.authors
    ) ;
GO 
--创建视图 和 上面比较
CREATE VIEW fnAuthorList_vw
AS
SELECT TOP 10
au_id ,
au_lname + ' ' + au_fname AS [NAME]
FROM      dbo.authors
GO

--这里UDF返回的和视图返回的一样! 但是UDF可以传参,但是视图不可以
SELECT  *
FROM    dbo.fnAuthorList( ) ;
--查询视图
SELECT  *
FROM    dbo.fnAuthorList_vw

GO

/*比?较?UDF 和?view */

-- view 
CREATE VIEW SalesCount_vw

AS
( SELECT    au.au_id ,
au.au_fname + ' ' + au.au_lname AS au_name ,
SUM(s.qty) AS SalesCount
FROM      dbo.authors AS au
INNER JOIN dbo.titleauthor AS ta ON ta.au_id = au.au_id
INNER JOIN dbo.sales AS s ON ta.title_id = s.title_id
GROUP BY  au.au_id ,
au.au_fname + ' ' + au.au_lname
) ;    
GO

SELECT  *
FROM    SalesCount_vw
WHERE   SalesCount > 25 ;

-- 视?图?这?里?不?能?作?为?参?数?传?入?
GO

-- UDF
CREATE FUNCTION dbo.SalesCount_fun ( @salesCount INT )
RETURNS TABLE
AS RETURN
    ( SELECT    au.au_id ,
                au.au_fname + ' ' + au.au_lname AS au_name ,
                SUM(s.qty) AS SalesCount
      FROM      dbo.authors AS au
                INNER JOIN dbo.titleauthor AS ta ON ta.au_id = au.au_id
                INNER JOIN dbo.sales AS s ON ta.title_id = s.title_id
      GROUP BY  au.au_id ,
                au.au_fname + ' ' + au.au_lname
      HAVING    SUM(s.qty) > @salesCount
    ) ;

GO
--这?里?直?接?作?为?参?数?传?入?

SELECT  *
FROM    dbo.SalesCount_fun(25) ;

--------------------------------------------------

--这里为经理和员工,但是他们只有一级的关系，其实有多级关系，Id=9的员工为id=5的员工的下级，

id = 5的员工为 id=2的下级, 你可以去掉下面的where条件看到他们的关系!当然在一个很大的公司

这种关系更复杂， 形成了递归关系。 

SELECT  DISTINCT
        emp.EmployeeID ,
        emp.ReportsTo ,
        mgr.EmployeeID ,
        emp.FirstName + ' ' + emp.LastName AS '员工' ,
        mgr.FirstName + ' ' + mgr.LastName AS '经理'
FROM    dbo.Employees AS emp
        INNER JOIN dbo.Employees AS mgr ON mgr.EmployeeID = emp.ReportsTo

WHERE   mgr.FirstName = 'Andrew'
        AND mgr.LastName = 'Fuller' 

--UDF中的递归调用 [最多32层] 查找下级的下级…

CREATE FUNCTION dbo.fnGetReports ( @employeeID AS INT )
RETURNS @reports TABLE
    (
      EmployeeID INT NOT NULL ,
      reportsToID INT NULL
    )
AS 
    BEGIN
        DECLARE @employee AS INT ;
        INSERT  INTO @reports
                SELECT  EmployeeID ,
                        ReportsTo
                FROM    dbo.Employees
                WHERE   EmployeeID = @employeeID ;
                
        SELECT  @employee = MIN(EmployeeID)
        FROM    dbo.Employees
        WHERE   ReportsTo = @employeeID ;
           
        WHILE @employee IS NOT NULL 
            BEGIN
                INSERT  INTO @reports
                        SELECT  *
                        FROM    dbo.fnGetReports(@employee) ;
                        
                SELECT  @employee = MIN(EmployeeID)
                FROM    dbo.Employees
                WHERE   EmployeeID > @employee
                        AND ReportsTo = @employeeID ;
                        
            END
            RETURN
    END
GO

SELECT * FROM fnGetReports(2) ; 
SELECT * FROM fnGetReports(5) ; 

--test

DECLARE @employeeID INT 
SELECT  @employeeID = EmployeeID
FROM    dbo.Employees
WHERE   LastName = 'Fuller' 

SELECT  emp.EmployeeID ,
        emp.LastName + ' ' + emp.FirstName AS 'employee' ,
        mgr.LastName AS 'reportsTo'
FROM    dbo.Employees AS emp
        INNER JOIN dbo.fnGetReports(@employeeID) AS gr ON emp.EmployeeID = gr.EmployeeID
        INNER JOIN dbo.Employees AS mgr ON mgr.EmployeeID = gr.reportsToID

//以上SQL语句来源<<SQL Server2005编程入门经典>>,只是本人学习笔记而已! UDF中的递归目前还不是很清楚， 只是贴出来供大家参考， 希望大家把UDF用的灵活的用到项目中去! 个人觉得还是比较强大的.

Technorati 标签: T-SQL,UDF,自定义函数