SQL自定义函数

1、v 函数综述函数综述v 系统函数系统函数v 用户自定义函数的基本操作用户自定义函数的基本操作v 创建用户自定义函数创建用户自定义函数v 查看用户自定义函数查看用户自定义函数v 修改用户自定义函数修改用户自定义函数v 删除用户自定义函数删除用户自定义函数v 函数综述函数综述 为了使用户对数据库进行查询和修改时更加方便，为了使用户对数据库进行查询和修改时更加方便，SQL server在在T-SQL语言中提供了许多内部函数以供调用。用户也可以根据自己的语言中提供了许多内部函数以供调用。用户也可以根据自己的需要创建函数。需要创建函数。 函数可以由系统提供，也可以由用户创建。系统提供的函数称为函数可以由

2、系统提供，也可以由用户创建。系统提供的函数称为内置函数，也叫做系统函数，它为用户方便快捷地执行某些操作提供内置函数，也叫做系统函数，它为用户方便快捷地执行某些操作提供帮助；用户创建的函数称为用户自定义函数，它是用户根据自己的特帮助；用户创建的函数称为用户自定义函数，它是用户根据自己的特殊需求而创建的，用来补充和扩展内置函数。殊需求而创建的，用来补充和扩展内置函数。 v 系统函数系统函数 标量函数标量函数 系统函数系统函数 标量函数标量函数 聚合函数聚合函数 行集函数。行集函数。 标量函数标量函数 标量函数对单一值操作，返回单一值。只要在能够使用表达式的标量函数对单一值操作，返回单一值。只要在能

3、够使用表达式的地方，就可以使用标量函数。地方，就可以使用标量函数。 数学函数数学函数 日期和时间函数日期和时间函数 字符串函数字符串函数 数据类型转换函数数据类型转换函数 。v 系统函数系统函数标量函数标量函数 数学函数数学函数 1、abs(数值型表达式数值型表达式) 功能：返回表达式功能：返回表达式 的绝对值，其值的数据类型与参数一致。的绝对值，其值的数据类型与参数一致。 例：例：SELECT ABS(-1), ABS(0), ABS(1) 2、ceiling(数值型表达式数值型表达式) 功能：返回最小的大于或等于给定数值型表达式的整数值，值的功能：返回最小的大于或等于给定数值型表达式的整数

4、值，值的 类型和给定的值相同。类型和给定的值相同。 floor(数值型表达式数值型表达式) 功能：返回最大的小于或等于给定数值型表达式的整数值。功能：返回最大的小于或等于给定数值型表达式的整数值。 例：例：SELECT FLOOR(123.45),CEILING(123.45) SELECT FLOOR(-123.45), CEILING(-123.45)v 系统函数系统函数标量函数标量函数 数学函数数学函数 3、SQUARE(float表达式表达式)函数函数 功能：此函数用于返回给定表达式的平方值功能：此函数用于返回给定表达式的平方值 例如：例如：SQUARE(3)的结果为的结果为9.0。

5、POWER（数值型表达式（数值型表达式1，数值型表达式，数值型表达式2）函数）函数 功能：此函数用于返回给定表达式乘指定次方的值。成方运算函功能：此函数用于返回给定表达式乘指定次方的值。成方运算函 数返回值的数据类型与第一个参数的数据类型相同。数返回值的数据类型与第一个参数的数据类型相同。 例如：例如：POWER(2,3)表示表示2的的3次方。次方。 SELECT POWER(2,-3),POWER(2.0,-3),POWER(2.000,-3) 4、sign(数值型表达式数值型表达式) 功能：判断数值的正负属性，功能：判断数值的正负属性，+1表示正数；表示正数；-1表示负数。表示负数。v 系

6、统函数系统函数标量函数标量函数 数学函数数学函数 5、 rand(整型表达式整型表达式) 功能：返回一个位于功能：返回一个位于0和和1之间的随机数，在单个查询中反复调用之间的随机数，在单个查询中反复调用 rand( )将产生相同的值。将产生相同的值。 例：例：DECLARE counter smallint SET counter = 1 WHILE counter =50 set retrunstr=取得学分取得学分 else set retrunstr=未取得学分未取得学分 return retrunstr END 如果使用用户自定义函数，要在使用的时候指明函数的所有者和函数的名如果使用用

7、户自定义函数，要在使用的时候指明函数的所有者和函数的名 称。在查询分析器中输入如下代码：称。在查询分析器中输入如下代码： SELECT 学号学号,成绩成绩,dbo.xuefen(成绩成绩) AS 学分情况学分情况 FROM 课程注册课程注册 WHERE 课程号课程号=0003 GOv 用户自定义函数用户自定义函数 2、使用企业管理器创建用户定义函数、使用企业管理器创建用户定义函数 在企业管理器中创建用户自定义函数的步骤为：在企业管理器中创建用户自定义函数的步骤为： 1）在企业管理器中，选择需要建立函数的数据库，从中选择用户定）在企业管理器中，选择需要建立函数的数据库，从中选择用户定义函数图标。

8、义函数图标。 2）选择用户定义函数图标后，击右健，从弹出的快捷菜单中选择）选择用户定义函数图标后，击右健，从弹出的快捷菜单中选择“新建用户定义函数新建用户定义函数”命令，打开命令，打开“用户定义函数属性用户定义函数属性”对话框，该属对话框，该属性窗口在性窗口在“文本文本”框中列出了建立函数的框架，如图框中列出了建立函数的框架，如图10.2所示。所示。 3）在）在“文本文本”框中输入函数的所有者、函数名称、参数列表、返回框中输入函数的所有者、函数名称、参数列表、返回类型和函数体等函数的各个组成部分。类型和函数体等函数的各个组成部分。 4）单击）单击“检查语法检查语法”按钮，检查输入的建立函数语句

9、是否有语法错按钮，检查输入的建立函数语句是否有语法错误。如果没有语法错误，单击误。如果没有语法错误，单击“确定确定”按钮，将用户定义的函数保存按钮，将用户定义的函数保存到数据库中供用户以后使用。到数据库中供用户以后使用。 v 用户自定义函数的分类用户自定义函数的分类-标量函数标量函数例：在例：在STUDENT库中创建一个用户自定义函数库中创建一个用户自定义函数XUEFENJI，该函数通过输，该函数通过输入成绩来计算学生的学分绩。其代码如下：入成绩来计算学生的学分绩。其代码如下：CREATE FUNCTION xuefenji(inputzz int) RETURNS nvarchar(10)

10、BEGIN declare retrunstr nvarchar(10) if inputzz =50 AND inputzz =60 AND inputzz =70 AND inputzz =80 AND inputzz =100 set retrunstr=学分绩为学分绩为1.5 else set retrunstr=无学分绩无学分绩 return retrunstr ENDv 用户自定义函数的分类用户自定义函数的分类标量函数标量函数例：例： USE STUDENT GO SELECT 课程号课程号,成绩成绩,dbo.xuefenji(成绩成绩) AS 学分绩学分绩 FROM 课程注册课程

11、注册 WHERE 学号学号=010101001001 GO 需要查看需要查看“010101001001”号学生的课程学分绩号学生的课程学分绩 v 用户自定义函数的分类用户自定义函数的分类表值函数表值函数表值函数遵循的原则：表值函数遵循的原则： 1、RETURNS子句仅包含关键字子句仅包含关键字table。不必定义返回变量的格式，因为它由。不必定义返回变量的格式，因为它由RETURN 子句中的子句中的 SELECT 语句的结果集的格式设置。语句的结果集的格式设置。 2、function_body 不由不由BEGIN和和END分隔。分隔。 3、RETURN子句在括号中包含单个子句在括号中包含单个S

12、ELECT语句。语句。SELECT语句的结果集构成语句的结果集构成函数所返回的表。内嵌表值函数中使用的函数所返回的表。内嵌表值函数中使用的SELECT语句受到与视图中使用的语句受到与视图中使用的SELECT语句相同的限制。语句相同的限制。例：在例：在STUDENT库中创建一个内嵌表值函数库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：的系部代码返回该系学生的基本信息。其代码如下：CREATE FUNCTION XUESHENG(inputxbdm nvarchar(4) RETURNS table AS RETURN

13、 ( SELECT 学号学号, 姓名姓名, 入学时间入学时间 FROM 学生学生 WHERE 系部代码系部代码=inputxbdm)GO建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：SELECT * FROM DBO.XUESHENG(01)GO v 用户自定义函数的分类用户自定义函数的分类多表值函数多表值函数例：在例：在STUDENT库中创建一个多语句表值函数库中创建一个多语句表值函数CHENGJI，该函数可以根据输入，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。其代码如下：的课程名称返回选修该课程的学生姓

14、名和成绩。其代码如下： CREATE FUNCTION CHENGJI( inputkc as char(20) ) RETURNS chji TABLE ( 课程名课程名 char(20), 姓名姓名 char(8), 成绩成绩 tinyint ) AS BEGIN INSERT chji SELECT c.课程名课程名,s.姓名姓名 ,k.成绩成绩 FROM 学生学生 as s INNER JOIN 课程注册课程注册 as k ON s.学号学号 =k.学号学号 inner join 课程课程 as c on c.课程号课程号=k.课程号课程号 WHERE c.课程名课程名=inputkc

15、 RETURN END GO在查询分析器中输入以下查询命令：在查询分析器中输入以下查询命令： SELECT * FROM DBO.CHENGJIi(大学语文大学语文)v 用户自定义函数的分类用户自定义函数的分类多表值函数多表值函数总结：总结：多语句函数的主体中允许使用以下语句。多语句函数的主体中允许使用以下语句。 1、赋值语句。、赋值语句。 2、控制流语句。、控制流语句。 3、DECLARE 语句，该语句定义函数局部的数据变量和游标。语句，该语句定义函数局部的数据变量和游标。 4、SELECT 语句，该语句包含带有表达式的选择列表，其中的表达语句，该语句包含带有表达式的选择列表，其中的表达式将

16、值赋予函数的局部变量。式将值赋予函数的局部变量。 5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以游标。只允许使用以INTO子句向局部变量赋值的子句向局部变量赋值的FETCH语句；不允语句；不允许使用将数据返回到客户端的许使用将数据返回到客户端的FETCH语句。语句。 6、INSERT、UPDATE和和DELETE语句，这些语句修改函数的局部语句，这些语句修改函数的局部table变量。变量。 7、EXECUTE语句调用扩展存储过程。语句调用扩展存储过程。 v 查看用户自定义函数的属性查看用户自定义函数的属性

17、查询分析器查询分析器在在SQL Server中，根据不同需要，可以使用中，根据不同需要，可以使用sp_helptext、sp_help等系等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：具体作用和语法如下：使用使用sp_helptext查看用户定义函数的文本信息，其语法格式为：查看用户定义函数的文本信息，其语法格式为： sp_helptext 用户自定义函数名用户自定义函数名使用使用sp_help查看用户自定义函数的一般信息，其语法格式为：查看用户自定义函数的一般信息，其语法格式为： sp_help

18、 用户自定义函数名用户自定义函数名例：使用有关系统过程查看例：使用有关系统过程查看STUDENT数据库中名为数据库中名为XUEFEN的用户的用户自定义函数的文本信息。其程序代码如下：自定义函数的文本信息。其程序代码如下： USE STUDENT GO SP_HELPTEXT XUEFEN GOv 查看用户自定义函数的属性查看用户自定义函数的属性企业管理器企业管理器在企业管理器中，用户可以很方便地查看用户定义函数的信息，其步在企业管理器中，用户可以很方便地查看用户定义函数的信息，其步骤为：骤为： 1）在企业管理器中，选择需要查看的函数所在的数据库。）在企业管理器中，选择需要查看的函数所在的数据库

19、。 2）在详细信息窗格中双击户定义函数图标，在详细窗格中显示出）在详细信息窗格中双击户定义函数图标，在详细窗格中显示出用户建立的函数名称。用户建立的函数名称。 3）右击需要查看的用户定义函数，从弹出的快捷菜单中选择）右击需要查看的用户定义函数，从弹出的快捷菜单中选择“属属性性”命令。命令。 4）当出现）当出现“用户定义函数数性用户定义函数数性”对话框时，在对话框时，在“文本文本”框中可以框中可以浏览用户定义函数的文本信息。单击浏览用户定义函数的文本信息。单击“权限权限”按钮查看用户定义函数按钮查看用户定义函数的权限。的权限。v 修改用户自定义函数的属性修改用户自定义函数的属性企业管理器企业管理

20、器1、使用企业管理器修改用户定义函数，在企业管理器中修改用户自、使用企业管理器修改用户定义函数，在企业管理器中修改用户自定义函数的步骤为：定义函数的步骤为： 1）在企业管理器中，选择需要修改的函数所在的数据库。）在企业管理器中，选择需要修改的函数所在的数据库。 2）在详细信息窗格中双击户定义函数图标，显示出用户建立的函）在详细信息窗格中双击户定义函数图标，显示出用户建立的函数名称。数名称。 3）右击需要修改的用户定义函数，从弹出的快捷菜单中选择）右击需要修改的用户定义函数，从弹出的快捷菜单中选择“属属性性”命令。命令。 4）当出现）当出现“用户定义函数数性用户定义函数数性”对话框时，在对话框时

21、，在“文本文本”框中修改框中修改用户定义函数的文本。用户定义函数的文本。 5）修改完后，单击）修改完后，单击“检查语法检查语法”按钮，语法检查通过后，单击按钮，语法检查通过后，单击“确定确定”按钮，完成函数修改。按钮，完成函数修改。v 修改用户自定义函数的属性修改用户自定义函数的属性查询分析器查询分析器2、使用、使用SQL命令修改用户自定义函数，使用命令修改用户自定义函数，使用ALTER FUNCTION 命命令可以修改用户自定义函数。修改由令可以修改用户自定义函数。修改由CREATE FUNCTION 语句创建语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：程或触发器。其语法格式如下：ALTER FUNCTION owner_n