第10章 函数

船在汹涌的波浪中行驶，固然是危险的 事，但只要把舵者善于应付，未尝不可 化险为夷，渡过大洋，安登彼岸。 一个年轻人的就业，也是如此，四周都 为困难所包围，你得镇静应付，把层层 障碍打破，便发现你的康庄大道。你须 知道，老天决不辜负有心人的上进志向， 除非你畏难苟安，无毅力应付，才会导 致完全失败的结果。 --戴尔· 卡耐基
14
数 据 库 技 术 与 应 用
15
数 据 库 技 术 与 应 用
例：在Northwind库上创建自定义函数 my_function1，该函数实现输入代表商品年销售 额的money类型参数的值后返回字符串，如果年销 售额大于10000，返回“热销商品”，否则返回 “非热销商品”
16
数 据 库 技 术 与 应 用
21
10.2.2 查看用户自定义函数
自定义函数的名称保存在sysobjects系统表中 创建自定义函数的源代码保存在syscomments系 统表中
22Leabharlann 数 据 库 技 术 与 应 用
1. 使用系统存储过程查看
EXEC sp_help | sp_helptext 函数名 例：用系统存储过程sp_helptext 查看用户自 定义函数my_funciton1的定义文本信息
28
数 据 库 技 术 与 应 用
10.3 用户自定义函数类型
标量函数
自定 义函 数有 三种 类型 内嵌表值函数
数 据 库 技 术 与 应 用
多语句表值函数
29
例：创建标量函数，要求将当前系统日期转化为年 月日格式的字符串并返回，且默认的分隔符为 ‘ :: ’ ，并允许用户自行定义分隔符
convert(数据类型，字段名，日期样式编号)
41
数 据 库 技 术 与 应 用
下面举例说明使用多语句表值函数my_function4分 别获取来自Paris的所有客户和供应商的信息。
42
数 据 库 技 术 与 应 用
本次课主要内容
第十章 • 10.1 • 10.2 • 10.3 • 10.4 用户自定义函数 用户自定义函数概述 创建用户自定义函数 用户自定义函数类型 修改和删除用户自定义函数
数 据 库 技 术 与 应 用
12
10.2.1
创建步骤与方法
13
数 据 库 技 术 与 应 用
例：创建一个自定义函数，计算某人到现在为止 的工龄工资
思路： 工龄工资=已工作年数 * 工作一年的工龄工资 已工作年数=当前时间 – 开始参加工作时间
工龄工资=(当前时间-开始参加工作时间) *年工龄工资
数 据 库 技 术 与 应 用
这些信息架构视图也是基于系统表sysobjects和 syscomments实现的
25
例：使用系统表sysobjects查看数据库 Northwind上存在的所有用户自定义函数的相关 信息。 USE Northwind SELECT * from sysobjects WHERE type='FN' go
35
例: 在Northwind数据库上建立一个可以根据输入
的城市名返回所有该城市的客户和供应商的信息的
内嵌表值函数my_function3
如果已有一个视图（城市中客户与供应商列表） “Customer and Suppliers by City”
数 据 库 技 术 与 应 用
36
37
数 据 库 技 术 与 应 用
45
数 据 库 技 术 与 应 用
10.4.2
删除
DROP FUNCTION [所有者.]函数名 例：删除在Northwind库上创建的自定义函数 my_function1 DROP FUNCTION my_function1
数 据 库 技 术 与 应 用
46
6
数 据 库 技 术 与 应 用
7
数 据 库 技 术 与 应 用
8
数 据 库 技 术 与 应 用
9
数 据 库 技 术 与 应 用
函数类型： • 内置函数 • 用户定义函数 内置函数 • 是一组预定义的函数，是 SQL语言的一部 分，按 SQL参考中定义的方式运行且不能 修改。
10
数 据 库 技 术 与 应 用
USE Northwind go EXEC sp_helptext my_function1 go
23
数 据 库 技 术 与 应 用
24
数 据 库 技 术 与 应 用
2. 使用系统表或系统信息架构视图
在SQL Server中存在 三个信息架构视图 报告有关用户自定 义函数的信息
ROUTINES PARAMETERS ROUTINE_COLUMNS
数 据 库 技 术 与 应 用
43
10.4 修改和删除用户自定义函数
10.4.1 修改
ALTER FUNCTION [ower-name,] function-name ([{ @parameter-name scalar-parameter-datetype [=default]}[,…n]]) RETURNS scalar-return-datatype [WITH <function-option> [,…n] ] [AS] BEGIN sql-statement RETURN scalar-expression END
30
数 据 库 技 术 与 应 用
31
数 据 库 技 术 与 应 用
CREATE FUNCTION my_function2 (@DATE datetime, @separator nvarchar(2)='::') RETURNS nvarchar(20) BEGIN DECLARE @returnstring nvarchar(20) SET @returnstring='今天是' +CONVERT(nvarchar(5),datepart(year,@date)) +‘年’+ @separator + CONVERT(nvarchar(5),datepart(month,@date)) +‘月’+ @separator + CONVERT(nvarchar(5),datepart(day,@date)) +'日' RETURN @returnstring END
• 是一组编译好的 SQL 语句，他们可以带一个或一 组数值做参数，也可以不带参数，它返回一个数 值、数值集合，或执行一些操作。 • 函数能够重复执行一些操作，从而避免不断重写 代码。
数 据 库 技 术 与 应 用
4
getdate() 得到当前系统日期时间 year() 得到日期参数的年份
5
数 据 库 技 术 与 应 用
38
数 据 库 技 术 与 应 用
下面示例使用新建立的内嵌表值函数 my_function3来获取“巴黎”市的所有客户和供 应商的信息
39
数 据 库 技 术 与 应 用
10.3.3 多语句表值函数
赋值 控制流 DECLARE SELECT 游标操作 INSERT UPDATE DELETE EXECUTE
40
允许 使用 的语 句
数 据 库 技 术 与 应 用
例: 在Northwind库上利用视图Customer and Suppliers by City（某一城市的客户 与供应商），建立一个可以根据输入的 城市名和用户，要求返回所有该城市的 客户或供应商的信息的多语句表值函数 my_function4，并将其与上例建立的内 嵌表值函数my_function3对比
32
数 据 库 技 术 与 应 用
如何调用新建立的标量函数呢？
可以运行如下命令： SELECT dbo.my_function2(GETDATE(),’--‘)
33
数 据 库 技 术 与 应 用
10.3.2 内嵌表值函数 Inline Table-valued Functions •在内嵌表值函数中，RETURN子句中包含有一条 单独的SELECT语句，该语句的结果构成了内嵌 表值函数所返回的表
可替代视图，可用在T-SQL 查询中允许表或视图表达 式的地方
34
数 据 库 技 术 与 应 用
视图：受限于单个 SELECT 语句，不允许包含用户 自己提供的参数
内嵌表值函数：可包含附加的语句，使函数所包含 的逻辑比视图的逻辑更强
返回表的内嵌表值函数还可替换返回单个结果集的 存储过程
数 据 库 技 术 与 应 用
44
数 据 库 技 术 与 应 用
例：修改在Northwind库上创建的自定义函数 my_function1，将区分商品是否热销的年销售额由 当初的10000提高到20000 ALTER FUNCTION my_function1 (@moneyinput money) RETURNS nvarchar(5) BEGIN DECLARE @returnstring nvarchar(5) IF @moneyinput<20000 SET @returnstring='非热销商品' ELSE SET @returnstring='热销商品' RETURN @returnstring END
数 据 库 技 术 与 应 用
26
本次课主要内容
第十章 • 10.1 • 10.2 • 10.3 • 10.4 用户自定义函数 用户自定义函数概述 创建用户自定义函数 用户自定义函数类型 修改和删除用户自定义函数
数 据 库 技 术 与 应 用
27
10.3.1 标量函数 Scalar functions 标量函数返回在 RETURNS子句中定义 的数据类型的单个数据值