SQL Server教程 - T-SQL-自定义函数（FUNCTION）

更新记录
转载请注明出处：
2022年8月1日 发布。
2022年7月2日 从笔记迁移到博客。

自定义函数说明（FUNCTION）

用户自定义函数可以像系统函数一样在查询或存储过程中调用，也可以像存储过程一样使用EXECUTE命令来执行。与编程语言中的函数类似，Microsoft SQL Server 2019 用户定义函数可以接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回。返回值可以是单个标量值或结果集。

注意：自定义函数不支持输出参数。

用户定义函数优点

1.允许模块化程序设计
只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。
2.执行速度更快
与存储过程相似，Transact-SQL用户定义函数通过缓存计划并在重复执行时重用它来降低Transact-SQL代码的编译开销。这意味着每次使用用户定义函数时均无须重新解析和重新优化，从而缩短了执行时间。和用于计算任务、字符串操作和业务逻辑的Transact-SQL函数相比，CLR函数具有显著的性能优势。Transact-SQL函数更适用于数据访问密集型逻辑。
3.减少网络流量对于无法用单个标量的表达式来表示的复杂约束（用来过滤数据的操作），就可以用函数形式来实现。然后，便可以在WHERE子句中调用这类函数，以减少发送至客户端的数字或行数。

自定义函数分类

汇总

标量函数： 返回一个确定类型的标量值
表值函数： 返回行列集合（结果集）
多语句表值函数

优点：
同存储过程
缺点：
同存储过程

标量函数

标量函数返回一个确定类型的标量值，对于多语句标量函数，定义在BEGIN END 块中的函数体包含一系列返回单个值的 Transact-SQL 语句。返回类型可以是除 text、ntext、image、cursor和timestamp外的任何数据类型。

表值函数

表值函数是返回数据类型为table的函数，内联表值函数没有由BEGIN END语句括起来的函数体，返回的表值是单个SELECT语句查询的结果。内联表值型函数功能相当于一个参数化的视图。对于多语句表值函数，在 BEGIN END 语句块中定义的函数体包含一系列Transact-SQL语句，这些语句可生成行并将其插入到返回的表中。

多语句表值函数

多语句表值函数可以看作标量型函数和表值函数的结合体。该函数的返回值是一个数据表，但它和标量值自定义函数一样，有一个用BEGINEND包含起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了表值自定义函数的不足。

与存储过程异同

相同点：
都是一组SQL语句
都可以有输出参数
函数可以有返回值，存储过程没有返回值
差异点：
函数可以用在SELECT语句中，而存储过程不可以
表值函数可以与其他表进行JOIN，而存储过程不可以

创建标量函数-使用T-SQL

返回标量的函数

CREATE FUNCTION [schema name.] 函数名(@参数名 数据类型  [ = 默认值 ] [READONLY],....)
RETURNS 返回类型
[WITH 函数选项]
AS
BEGIN
    -- SQL语句
    RETURN 值;
END

function_name:用户定义函数的名称。
＠parameter_name:用户定义函数中的参数。可声明一个或多个参数（一个函数最多可以有2100个参数。执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值）。
parameter_data_type:参数的数据类型。
［＝default]:参数的默认值。
return_data_type:标量用户定义函数的返回值。
function_body:指定一系列定义函数值的Transact-SQL语句。function_body 仅用于标量函数和多语句表值函数。
RETURN scalar_expression:指定标量函数返回的标量值。
注意：函数定义必须是批处理的第一个语句，可以使用GO定义批处理即可

实例：

CREATE FUNCTION GetStuNameById(@stuid INT)
RETURNS VARCHAR(30)
AS
BEGIN
	DECLARE @stuName CHAR(30)
	SELECT @stuName=(SELECT s name FROM stu info WHERE s id=@stuid)
	RETURN @stuName
END

创建表值函数-使用T-SQL

表值函数与标量函数不同，返回的是一张表

CREATE FUNCTION [schema name.]表名 (@参数名 数据类型  [ = 默认值 ] [READONLY],.....)
RETURNS TABLE
AS
BEGIN
    -- SQL语句
    RETURN (SELECT 列名,.... FROM 表名 WHERE 条件);
END

注意：函数定义必须是批处理的第一个语句，可以使用GO定义批处理即可

实例：

CREATE FUNCTION getStuRecordBySex(@stuSex CHAR(2) )
RETURNS TABLE
AS
BEGIN
RETURN
    （
    SELECT s id, s name,s sex, (s score-10) AS newScore
    FROM stu info
    WHERE S sex=@stuSex
    ）
END

创建函数-使用SSMS

执行函数-使用T-SQL

架构名.函数名(参数)

使用方法：
自定义函数可以在SELECT语句中、也可以在WHERE语句中
自定义函数也可以用EXECUTE进行执行，但必须指定返回值
注意：架构名不可以省略，默认的架构名为dbo，即：dbo.函数名()

实例：SELECT调用自定义的函数

SELECT dbo.PandaFunction(2,'Dog6666');

注意：记得要带dbo这个默认架构名，或者你定义的架构名

实例：EXECUTE调用自定义函数

DECLARE @result CHAR(30);
DECLARE @result2 CHAR(30);
EXECUTE @result = PandaFunction 1,'Dog666';
EXECUTE @result2 = PandaFunction @VAR1 = 1, @VAR2 = 'Dog666';
SELECT @result,@result2;

注意：函数没有()符号
注意：参数可以使用形参也可以不用形参

删除函数-使用T-SQL

DROP FUNCTION 函数名