CREATE FUNCTION (Transact-SQL)

語法

Scalar Functions
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
[ ; ]

Inline Table-Valued Functions
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 [ ) ]
[ ; ]

Multistatement Table-valued Functions
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
[ ; ]

CLR Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ =default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR Table-Valued Functions
CREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ =default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

Method Specifier<method_specifier>::=    assembly_name.class_name.method_nameFunction Options<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) <clr_table_type_definition>::= ( { column_name data_type } [ ,...n ] )<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed ,increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}

<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      [ WITH FILLFACTOR =fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=column_name AS computed_column_expression<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      (column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK (logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

引數

• schema_name
  這是使用者定義函數所屬的結構描述名稱。

• function_name
  這是使用者定義函數的名稱。函數名稱必須符合識別碼的規則；另外，函數名稱在資料庫內必須是唯一的，且對於它的結構描述也必須是唯一的。

  [!附註]

  即使沒有指定參數，函數名稱後面仍需要括號。

• @parameter\_name
  這是使用者定義函數中的參數。可以宣告一個或多個參數。

  函數最多可以有 2,100 個參數。除非定義了參數的預設值，否則在執行函數時，使用者必須提供每個已宣告之參數的值。

  使用 @ 記號 (@) 當做第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對函數而言，參數必須是本機參數；相同的參數名稱可以用在其他函數中。參數只能取代常數，不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。

  [!附註]

  在預存程序或使用者定義函數中傳遞參數，或在批次陳述式中宣告和設定變數時，不接受 ANSI_WARNINGS。例如，如果變數定義為 char(3)，然後又設為超過 3 個字元的值，就會將資料截斷成定義的大小，而 INSERT 或 UPDATE 陳述式會繼續運作。

• [ type_schema_name.] parameter_data_type
  這是參數資料類型，對於其所屬的結構描述而言為選擇性。就 Transact-SQL 函數而言，除了 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者定義型別和使用者定義資料表類型) 都是允許的資料類型。就 CLR 函數而言，除了 text、ntext、image、使用者定義資料表類型和 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型。非純量類型 cursor 和 table 不能指定為 Transact-SQL 或 CLR 函數中的參數資料類型。

  如果未指定 type_schema_name，Database Engine 會依照下列順序來尋找 scalar_parameter_data_type：

  • 內含 SQL Server 系統資料類型名稱的結構描述。

  • 目前資料庫中之目前使用者的預設結構描述。

  • 目前資料庫中的 dbo 結構描述。

• [ =default ]
  這是參數的預設值。如果定義了 default 值，不必指定該參數的值就可以執行函數。

  [!附註]

  可以針對 CLR 函數指定預設參數值，但 varchar(max) 和 varbinary(max) 資料類型除外。

  如果函數的參數有預設值，則必須在呼叫函數來擷取該預設值時指定關鍵字 DEFAULT。這個行為與使用預存程序中具有預設值的參數不一樣，因為在預存程序中，省略參數也意味著使用預設值。

• READONLY
  指示無法在函數的定義內更新或修改參數。如果參數類型是使用者定義資料表類型，應該指定 READONLY。

• return_data_type
  這是純量使用者定義函數的傳回值。就 Transact-SQL 函數而言，除了 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型。就 CLR 函數而言，除了 text、ntext、image 及 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型。非純量類型 cursor 和 table 不能指定為 Transact-SQL 或 CLR 函數中的傳回資料類型。 

• function_body
  指定一系列 Transact-SQL 陳述式 (這些陳述式聯合後不會產生資料表修改之類的副作用) 負責定義函數的值。function_body 只能用在純量函數和多重陳述式資料表值函數中。

  在純量函數中，function_body 是一系列的 Transact-SQL 陳述式，這些陳述式會一起評估為純量值。

  在多重陳述式資料表值函數中，function_body 是一系列的 Transact-SQL 陳述式，這些陳述式會擴展 TABLE 傳回變數。

• scalar_expression
  指定純量函數傳回的純量值。

• TABLE
  指定資料表值函數的傳回值是資料表。只有常數和 @local\_variables 可傳遞至資料表值函數。

  在內嵌資料表值函數中，TABLE 傳回值是利用單一 SELECT 陳述式所定義。內嵌函數沒有相關聯的傳回變數。

  在多重陳述式資料表值函數中，@return_variable 是一個 TABLE 變數，可用來儲存及累積應該當做函數值來傳回的資料列。只能為 Transact-SQL 函數 (不能為 CLR 函數) 指定 @return\_variable。

• select_stmt
  這是單一 SELECT 陳述式，可定義嵌入資料表值函數的傳回值。

• ORDER (<order_clause>)
  指定從資料表值函數中傳回結果的順序。如需詳細資訊，請參閱本主題稍後的「使用排序次序的指引」一節。

• EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
  指定組件與函數繫結的方法。assembly_name 必須符合目前資料庫之 SQL Server 中的現有組件且可以看見。class_name 必須是有效的 SQL Server 識別碼，且必須當做類別存在於組件中。如果該類別的名稱符合命名空間，且該名稱利用句點 (.) 來分隔命名空間的各個部分，您就必須使用方括號 ([]) 或引號 ("") 來分隔類別名稱。method_name 必須是有效的SQL Server 識別碼，且必須當做靜態方法存在於指定的類別中。

  [!附註]

  依預設，SQL Server 不能執行 CLR 程式碼。您可以建立、修改和卸除參考 Common Language Runtime 模組的資料庫物件；不過，在啟用 clr enabled 選項之前，您無法在 SQL Server 中執行這些參考。若要啟用這個選項，請使用 sp_configure。

• <table_type_definition> ( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n] )
  定義 Transact-SQL 函數的資料表資料類型。資料表宣告包括資料行定義和資料行或資料表條件約束。資料表一律放在主要檔案群組中。

• < clr_table_type_definition > ( { column_namedata_type } [ ,...n] )
  定義 CLR 函數的資料表資料類型。資料表宣告只包含資料行名稱和資料類型。資料表一律放在主要檔案群組中。

最佳作法

如果未以 SCHEMABINDING 子句建立使用者定義函數，叫用該函數時，對基礎物件所進行的變更可能會影響函數的定義並產生非預期的結果。建議您實作下列其中一個方法，以確保函數不會因為其基礎物件的變更而變成過期：

• 當您要建立函數時，指定 WITH SCHEMABINDING 子句。這可以確保系統無法修改函數定義中參考的物件 (除非同時修改函數)。

• 修改在函數定義中指定的任何物件之後，執行 sp_refreshsqlmodule 預存程序。

資料類型

如果在 CLR 函數中指定參數，這些參數應該是 SQL Server 類型，如同先前針對 scalar_parameter_data_type 所下的定義所示。如需有關比較 SQL Server 系統資料類型與 CLR 整合資料類型或 .NET Framework Common Language Runtime 資料類型的資訊，請參閱＜對應 CLR 參數資料＞。

當正確的方法在類別中多載時，若要讓 SQL Server 參考正確的方法，<method_specifier> 中所指出的方法必須具有下列特性：

• 接收與 [ ,...n ] 中所指定的數目相同的參數。].

• 依值 (而不是依參考) 接收所有參數。

• 使用與 SQL Server 函數中指定之類型相容的參數類型。

如果 CLR 函數的傳回資料類型指定資料表類型 (RETURNS TABLE)，<method_specifier> 中之方法的傳回資料類型應該屬於 IEnumerator 或 IEnumerable 類型，且假設介面是由函數建立者所實作。與 Transact-SQL 函數不同，CLR 函數不能將 PRIMARY KEY、UNIQUE 或 CHECK 條件約束併入 <table_type_definition> 中。<table_type_definition> 中指定的資料行資料類型必須符合相對應的資料行類型，該資料行是 <method_specifier> 中的方法在執行階段傳回之結果集的資料行。這項類型檢查作業不會在建立函數時執行。

如需有關如何編寫 CLR 函數的詳細資訊，請參閱＜CLR 使用者定義函數＞。

一般備註

純量值函數可在使用純量運算式的情況下進行叫用。這包括計算資料行和 CHECK 條件約束定義。您也可以利用 EXECUTE 陳述式來執行純量值函數。叫用純量值函數必須至少使用函數的兩部分名稱。如需有關多部分名稱的詳細資訊，請參閱＜Transact-SQL 語法慣例 (Transact-SQL)＞。在 SELECT、INSERT、UPDATE 或 DELETE 陳述式的 FROM 子句中允許資料表運算式的情況下，就可以叫用資料表值函數。如需詳細資訊，請參閱＜執行使用者定義函數 (Database Engine)＞。

互通性

以下是函數中的有效陳述式：

• 指派陳述式。

• 流程控制陳述式 (但不包括 TRY...CATCH 陳述式)。

• DECLARE 陳述式 - 定義本機資料變數和本機資料指標。

• SELECT 陳述式 - 包含選取清單，清單中含有指派值給本機變數的運算式。

• 資料指標作業 - 參考函數中之已宣告、已開啟、已關閉及已取消配置的本機資料指標。只允許利用 INTO 子句指派值給本機變數的 FETCH 陳述式，不允許將資料傳送至用戶端的 FETCH 陳述式。

• 修改本機資料表變數的 INSERT、UPDATE 及 DELETE 陳述式。

• 呼叫擴充預存程序的 EXECUTE 陳述式。

• 如需詳細資訊，請參閱＜建立使用者定義函數 (Database Engine)＞。

計算資料行的互通性

從函數呼叫擴充預存程序

限制事項

使用者定義函數不能用來執行修改資料庫狀態的動作。

使用者定義函數不得包含具有資料表當做其目標的 OUTPUT INTO 子句。

下列 Service Broker 陳述式不能併入 Transact-SQL 使用者定義函數的定義中：

• BEGIN DIALOG CONVERSATION

• END CONVERSATION

• GET CONVERSATION GROUP

• MOVE CONVERSATION

• RECEIVE

• SEND

使用者定義函數可以具有巢狀結構；也就是說，某個使用者定義函數可以呼叫另一個使用者定義函數。被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。使用者定義函數所建立的巢狀結構最多可以有 32 個層級。超過巢狀層級上限會導致整個呼叫函數鏈結失敗。依照 32 個層級巢狀限制，Transact-SQL 使用者定義函數之 Managed 程式碼的任何參考都算是一個層級。從 Managed 程式碼內叫用的方法，不列入這項限制。

在 CLR 資料表值函數中使用排序次序

中繼資料

下表列出您可以用來傳回使用者定義函數相關之中繼資料的系統目錄檢視表。

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO

權限

需要資料庫中的 CREATE FUNCTION 權限，以及建立此函數所在之結構描述上的 ALTER 權限。如果此函數指定使用者定義型別，則需要該型別上的 EXECUTE 權限。

範例

A. 使用計算 ISO 週的純量值使用者定義函數

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

B. 建立嵌入資料表值函數

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 建立多重陳述式資料表值函數

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

D. 建立 CLR 函數

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

請參閱

參考

其他資源

變更記錄