自定义函数
1.用户自定义函数与存储过程的比较
| 比较项 |用户自定义函数 |存储过程|
|:------:|:-----:-| :-----:-|
|参数|允许有0到多个输入参数，不允许有输出参数|允许有多个输入/输出参数|
|返回值|有且只有一个返回值|可以没有返回值|
| 调用|在表达式或赋值语句中引用|使用EXECUTE调用|
|其他|可以返回表变量，但不能使用OUTPUT参数，不能使用临时表，不能通过select返回结果集|不能返回表变量|
2.自定义函数分类
根据函数返回值的不同，自定义函数可分为标量值函数和表值函数。具体如下。
(1)标量值函数
标量值函数的返回值是返回子句(RETURNS子句)中定义的类型的单个数据值，不能返回多个值。
(2)内嵌表值函数
内嵌表值函数返回的是在RETURNS子句中指定的“table”类型的数据行集(表)。在内嵌表值函数中，RETURNS子句在括号中含有一条单独的SELECT查询语句，该语句的结果构成了内嵌表值函数所返回的表。
内嵌表值函数可以替代视图，并且比视图的逻辑功能更强大。在T-SQL查询中允许使用表或视图表达式的地方，也可以使用内嵌表值函数。
内嵌表值函数还可以替代返回单个结果集的存储过程。内嵌表值函数返回的表可在T-SQL语句的FROM子句中被引用，而存储过程返回的结果集不能被引用。
(3)多语句表值函数
与内嵌表值函数一样，多语句表值函数返回的是由选择结果构成的数据行集(表)。与内嵌表值函数不同的是，多语句表值函数在返回语句之前还有其他的T-SQL语句，并且RETURNS子句指定的表带有列及其他数据类型。
创建自定义函数
（1）创建标量值函数
CREATE [ OR ALTER ] 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
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
- 返回类型不能是text、ntext、image、cursor、timestamp和表类型；
- READONLY：指示不能在函数定义中更新或修改参数。 如果参数类型为用户定义的表类型，则应指定 READONLY。
- return_data_type：标量用户定义函数的返回值。
- scalar_expression：指定标量函数返回的标量值。
- 在BEGIN...END之间的语句是函数体，函数体中国必须包含一条不带参数的RETURN语句用于返回表。
【示例】
CREATE FUNCTION EProduct_Number
(@EP_CID int)
RETURNS smallint
AS
BEGIN
DECLARE @epnum smallint
SELECT @epnum = count(*) FROM EProduct WHERE CID=@EP_CID
RETURN @epnum
END
GO
（2）创建内联表值函数
CREATE [ OR ALTER ] 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 [ ) ]
- 内联表值函数没有函数体。
- TABLE：指定表值函数的返回值为表。
- 在内联表值函数中，TABLE 返回值是通过单个 SELECT 语句定义的。 内联函数没有关联的返回变量。
【示例】
CREATE FUNCTION EProduct_Table
(@EP_CID int)
RETURNS Table
AS
RETURN(SELECT ENO,EName FROM EProduct WHERE cid=@EP_CID)
若要调用该函数，请运行此查询：
SELECT * FROM EProduct_Table(1)
（3）多语句表值函数
与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的T-SQL语句，具体的语法格式如下：
CREATE [ OR ALTER ] 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
<table_type_definition>:: =
( { <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ ,...n ]
)
- 在多语句表值函数中，@return_variable是表变量，用于存储和累积应作为函数的值返回的行。该变量的数据类型是Table，而且在该子句中还需要对返回的表进行表结构的定义。
- 在BEGIN...END之间的语句是函数体，函数体中国必须包含一条不带参数的RETURN语句用于返回表。
【示例】
CREATE FUNCTION EProduct_Table_1
(@EP_CID int)
RETURNS @tb Table
(
tb_Eno char(11),
tb_EName varchar(50),
tb_EJoinData DateTime
)
AS
BEGIN
INSERT INTO @tb SEELCT ENO,EName,EJoinDate FROM EProduct WHERE CID = @EP_CID
RETURN
END
限制和局限
①用户定义函数不能用于执行修改数据库状态的操作。
②用户定义函数不能包含将表作为其目标的 OUTPUT INTO 子句。
函数的管理
①删除函数
DROP FUNCTION [ IF EXISTS ] { [ schema_name. ] function_name } [ ,...n ]
