MSSQL 自定义函数详解

自定义函数分为：标量值函数或表值函数

如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。
如果 RETURNS 子句指定 TABLE，则函数为表值函数。
表值函数又可分为：内嵌表值函数（行内函数）或多语句函数

如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数。
标量值函数示例

CREATE FUNCTION dbo.Foo()RETURNS INTASBEGINDECLARE @n INT SELECT @n = 3 RETURN @nEND 

内嵌表值函数示例

CREATE FUNCTION dbo.Foo()RETURNS TABLEASRETURN SELECT id,       titleFROM   msgs 

多语句表值函数示例（部分）

CREATE FUNCTION dbo.fn_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.*/ASBEGIN   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    RETURNEND;GO在以下示例中，调用了此函数。-- Example invocationSELECT EmployeeID, Name, Title, EmployeeLevelFROM dbo.fn_FindReports(109)ORDER BY Sort;

注意其 RETURNS 部分。

多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。
赋值语句。
控制流语句。
DECLARE 语句，该语句定义函数局部的数据变量和游标。
SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句；不允许使用将数据返回到客户端的 FETCH 语句。
INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。
EXECUTE 语句调用扩展存储过程。

ALTER FUNCTION [dbo].[AmountRole1]  (      @Amount numeric(18,2)  )  RETURNS numeric(18,2)  AS  BEGIN      DECLARE @i FLOAT      IF @Amount >= 2000          SET @i = @Amount * 0.98 --满2000打9.8折        ELSE           SET @i = @Amount       RETURN (@i)  END 

自定义函数语法

Scalar FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS return_data_type    [ WITH <function_option> [ ,...n ] ]    [ AS ]    BEGIN                 function_body         RETURN scalar_expression    END[ ; ]Inline Table-valued FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS TABLE    [ WITH <function_option> [ ,...n ] ]    [ AS ]    RETURN [ ( ] select_stmt [ ) ][ ; ]Multistatement Table-valued FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS @return_variable TABLE < table_type_definition >    [ WITH <function_option> [ ,...n ] ]    [ AS ]    BEGIN                 function_body         RETURN    END[ ; ]CLR FunctionsCREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type         [ = default ] }     [ ,...n ])RETURNS { return_data_type | TABLE <clr_table_type_definition> }    [ WITH <clr_function_option> [ ,...n ] ]    [ AS ] EXTERNAL NAME <method_specifier>[ ; ]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 } }