11第11章用户自定义函数

于RETURN子句中的SELECT命令段从数据库 中筛选出来。内联表值型函数功能相当于一个参数
化的视图。
» 6
第11章 用户自定义函数
多语句表值型函数 （Multi-statement table-valued functions）
多语句表值型函数可以看作标量型和内嵌表值型 函数的结合体。它的返回值是一个表，但它和标量型 函数一样有一个用 BEGIN-END 语句括起来的函数体， 返回值的表中的数据是由函数体中的语句插入的。由 此可见，它可以进行多次查询，对数据进行多次筛选 与合并，弥补了内嵌表值型函数的不足。
» 7
第11章 用户自定义函数
用户定义函数采用零个或更多的输入参数并返回标量 值或表。 函数最多可以有1024个输入参数。当函数的参数有默 认值时，调用该函数时必须指定默认DEFAULT关键 字才能获取默认值。该行为不同于在存储过程中含有 默认值的参数，而在这些存储过程中省略该函数也意 味着省略默认值。用户定义函数不支持输出参数。
标量型函数（Scalar
functions） table-valued functions）
内嵌表值型函数（Inline
多语句表值型函数（Multi-statement
table-
valued functions）。
» 4
第11章 用户自定义函数
标量型函数（Scalar functions） 标量型函数返回在RETURNS子句中定义的类型的 单个数据值。在BEGIN...END块中定义的函数主体 包含返回该值的 Transact-SQL语句系列。返回类
11.2 创建用户定义函数
创建内联表值型函数，返回指定学院学生的信息
USE 教学管理
GO CREATE FUNCTION F_学生信息(@院系 CHAR(20))
RETURNS TABLE
AS RETURN(
SELECT 学号, 姓名, 性别, 院系, 专业, 籍贯
FROM 学生表 WHERE 所在院系=@院系 )
第11章 用户自定义函数
第11章 用户自定义函数设计
11.1 用户自定义函数概述 11.2 创建用户定义函数 11.3 用户定义函数的调用 11.4 修改和删除用户定义函数
» 1
第11章 用户自定义函数
11.1 用户自定义函数概述
除了使用系统提供的函数外，用户还可以根据需要自
定义函数。用户自定义函数（User Defined Functions）
USE 教学管理
GO CREATE FUNCTION F_成绩分级(@成绩 FLOAT) RETURNS CHAR(16) AS
» 18
第11章 用户自定义函数
11.2 创建用户定义函数
BEGIN DECLARE @等级 CHAR(16) SELECT @等级 = CASE WHEN @成绩 IS NULL THEN '还没参加考试' WHEN @成绩 < 60 THEN '不及格'
WHEN @成绩 >= 60 and @成绩 < 70 THEN '及格'
WHEN @成绩 >= 70 and @成绩 < 80 THEN '中等' WHEN @成绩 >= 80 and @成绩 < 90 THEN '良好' ELSE '优秀!' END RETURN(@等级)
END
» 19
第11章 用户自定义函数
» 14
第11章 用户自定义函数
参数说明：
scalar_parameter_data_type ： 参 数 的 数 据 类 型 。 所 有 标 量 数 据 类 型 （ 包 括 bigint 和 sql_variant ） 都 可 用 作 用 户 定 义 函 数 的 参 数 。 不 支 持 timestamp 数 据 类 型和用户定义数据类型。不能指定非标量类型 （ 例 如cursor和table）。 scalar_return_data_type ： 是 标 量 用 户 定 义 函 数 的 返 回 值 。 scalar_return_data_type 可 以 是 SQL Server 支 持 的 任 何 标 量 数 据 类 型 （ text 、 ntext 、 image和timestamp除外）。 scalar_expression：指定标量型函数返回的标量值。 TABLE：指定表值型函数的返回值为表。在内嵌表值型函数中， 通过单个SELECT语句定义TABLE返回值。 内嵌函数没有相关联 的返回变量。在多语句表值 型函数中，@return_variable 是TABLE变量， 用于存储和累积应作为函数值返回的行。
CREATE FUNCTION语句中可以声明一个或多个参数。函数 最多可以有1 024个参数。函数执行时每个已声明参数的值必 须由用户指定，除非该参数的默认值已经定义。如果函数的参 数有默认值，在调用该函数时必须指定“default”关键字才能 获得默认值。这种行为不同于存储过程中有默认值的参数，在 存储过程中省略参数也意味着使用默认值。使用@符号作为第 一个字符来指定参数名称。参数名称必须符合标识符的规则。 每个函数的参数仅用于该函数本身；相同的参数名称可以用在 其它函数中。参数只能代替常量；而不能用于代替表名、列名 或其它数据库对象的名称。
» 9
第11章 用户自定义函数
11.2
创建用户定义函数
创建标量型用户自定义函数（Scalar functions）：
CREATE FUNCTION 函数名 ([{ @参数 [AS] 数据类型 [ = default ] } [ ,...n ] ] ) RETURNS 数据类型 [WITH < function_option> [ [,] ...n] ] AS BEGIN 函数体 RETURN 表达式 END
» 15
第11章 用户自定义函数
参数说明：
function_body：指定一系列Transact-SQL语句定义函数的值， 这些语句合在一起不会产生副作用。function_body只用于标量 型函数和多语句表值型函数。在标量型函数中，function_body是 一系列合起来求得标量值的Transact-SQL语句。在多语句表值型函 数中，function_body是一系列填充表返回变量的Transact-SQL语 句。 select-stmt：是定义内嵌表值型函数返回值的单个SELECT语句。 ENCRYPTION指出SQL Server加密包含CREATE FUNCTION语句文本的 系统表列。使用ENCRYPTION可以避免将函数作为SQL Server复制的 一部分发布。 SCHEMBINDING：指定将函数绑定到它所引用的数据库对象。如果 函数是用SCHEMABINDING选项创建的，则不能更改（使用ALTER语句） 或除去（使用DROP语句）该函数引用的数据库对象。
» 11
第11章 用户自定义函数
创建多语句表值型函数 （ Multi-statement table-valued functions）：
CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] AS BEGIN function_body RETURN END < function_option > ::= { ENCRYPTION | SCHEMABINDING } < table_type_definition > ::= ( { column_definition | table_constraint } [ ,...n ] )
» 10
第11章 用户自定义函数
创建内嵌表值型函数 （Inline table-valued functions）：
CREATE FUNCTION 函数名 ([{@parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [WITH < function_option > [ [,] ...n ] ] AS RETURN [ SELECT 语句 ]
» 20
第11章 用户自定义函数
11.2 创建用户定义函数
创建多语句表值型函数，返回指定教师某学年的开课信息 USE 教学管理 GO CREATE FUNCTION F_ 教师课表 (@ 教师姓名 CHAR(8),@ 开课学年 char(9)) RETURNS @教师课表 TABLE( 课名 varchar(30),开课地点 char(6), 开课学年 char(9),开课学期 int,
Transact-SQL语句中使用某个函数之前，必须先给此用
户授予该函数的适当权限。若要创建或更改在CHECK 约束、DEFAULT子句或计算列定义中引用用户定义函 数的表，还必须具有函数的REFERENCES权限。
» 3
第11章 用户自定义函数
在SQL Server 中根据函数返回值形式的不同将用 户自定义函数分为三种类型：
是SQL Server 2000以后新增的数据库对象，是SQL Server的一大改进。
用户自定义函数不能用于执行一系列改变数据库状态
的操作，但它可以像系统函数一样在查询或存储过程等的 程序段中使用，也可以像存储过程一样通过EXECUTE命令 来执行。用户自定义函数中存储了一个Transact-SQL例程， 可以返回一定的值。