Sql Server系列：自定義函式

libingql發表於2014-12-16
SQLServer函式

　　使用者自定義函式可以像系統函式一樣在查詢或儲存過程中呼叫，可以接受引數、執行操作並將操作結果以值的形式返回。返回值可以是單個標量或結果集。

1. 標量函式

　　標量函式返回一個確定型別的標量值，對於多語句的標量函式，定義在BEGIN END塊中的函式體包含一系列返回單個值的T-SQL語句。

　　建立標量函式語法結構：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductNameByProductID]
(
    @ProductID INT
)
    RETURNS VARCHAR(50)
AS
    BEGIN
        DECLARE @ProductName VARCHAR(50)

        SELECT @ProductName = [ProductName]    FROM [dbo].[Product]
        WHERE [ProductID] = @ProductID

        RETURN @ProductName
    END
SELECT [dbo].[FN_GetProductNameByProductID](1)
-- SELECT dbo.FN_GetProductNameByProductID(1)

2. 表值函式

　　表值函式是返回資料型別為table的函式，返回的表值是單個SELECT語句查詢的結果。

　　建立表值函式語法結構：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[FN_GetProductsByCategoryID]
(
    @CategoryID INT
)
    RETURNS TABLE
AS
    RETURN
    (
        SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
        FROM [dbo].[Product]
        WHERE [CategoryID] = @CategoryID
    )
SELECT * FROM [dbo].[FN_GetProductsByCategoryID](1)

3. 多語句表值函式

　　多語句表值函式可以看作標量型函式和表值函式的結合體。該函式的返回值是一個表，但它和標量值自定義函式用於，有一個用BEGIN END包含起來的函式體。返回值的表中資料是由函式體中的語句插入的。多語句表值函式可以進行多次查詢，對資料進行多次篩選與合併，彌補了表值自定義函式的不足。

　　多語句表值函式語法結構：

CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

　　示例：

CREATE FUNCTION [dbo].[UFN_GetAllChildren]
(
    @CategoryID INT
)
RETURNS @Result TABLE 
(
    [CategoryID] INT NOT NULL,
    [CategoryName] VARCHAR(50) NULL,
    [ParentID] int NULL,
    [Level] int NULL
)
AS
    BEGIN
        WITH CTE AS
        (
            SELECT [CategoryID],[CategoryName],[ParentID],0 AS [Level]
            FROM [dbo].[Category]
            WHERE [CategoryID] = 2
            UNION ALL
            SELECT [dbo].[Category].[CategoryID],[dbo].[Category].[CategoryName],[dbo].[Category].[ParentID],[Level] + 1
            FROM CTE INNER JOIN [dbo].[Category]
            ON CTE.[CategoryID] = [dbo].[Category].[ParentID]
        )

        INSERT INTO @Result([CategoryID],[CategoryName],[ParentID],[Level])
        SELECT [CategoryID],[CategoryName],[ParentID],[Level] FROM CTE

        RETURN
    END
SELECT * FROM [dbo].[UFN_GetAllChildren](2)

4. 刪除函式

DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ] 
DROP FUNCTION [dbo].[FN_GetAllChildren]

相關文章