Loading

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

image

执行函数-使用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 函数名
posted @ 2022-08-01 09:37  重庆熊猫  阅读(909)  评论(0编辑  收藏  举报