首页 > 代码库 > SqlServer 用户自定义函数
SqlServer 用户自定义函数
在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。可以使用CREATE FUNCTION 语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。
用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。
自定义函数分为 标量函数、 表值函数、 多语句表值函数 三种。
（1）标量函数：对单一值操作，返回单一值。只要在能够使用表达式的地方，就可以使用标量函数。
（2）表值函数：返回值是一个记录集合——表。在此函数中，return语句包含一条单独的select语句。
（3）多语句表值函数：返回值是由选择的结果构成的记录集。
一：标量函数
格式如下：
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
解释如下：
（1）function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。
（2）@parameter_name：用户自定义函数的参数。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。
（3）scalar_parameter_data_type：参数的数据类型。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。
（4）scalar_return_data_type：是用户定义函数的返回值。数据类型与（3）输入参数的要求相同。不能有text、ntext、image 和 timestamp类型的参数。
（5）function_body：位于begin和end之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。
（6）scalar_expression：用户自定义函数中返回值的表达式。
如果你觉得复杂，可以简化为
CREATE FUNCTION function_name（函数名）
( @参数1名 参数1数据类型[ = default ], @参数2名 参数2数据类型[ = default ],......)
RETURNS 返回数据类型
[ AS ]
BEGIN
sql语句
RETURN scalar_expression（返回值）
END
例：在STUDENT库中创建一个用户自定义函数XUEFEN，该函数通过输入成绩来判断是否取得学分，当成绩大于等于50时，返回取得学分，否则，返回未取得学分。其代码如下：
CREATE FUNCTION xuefen(@inputxf int) --(1) 函数名为 xuefen，输入参数为@inputxf ， 参数数据类型为int
RETURNS nvarchar(10) --(2) 函数的返回值类型为nvarchar(10)
BEGIN --(3) 将sql语句放在 BEGIN 和 END 之间，使其作为一个整体。
declare @retrunstr nvarchar(10) --(4) 声明函数返回值的变量为@retrunstr，数据类型为nvarchar(10)；该类型要与 (2)中声明的保持一致
If @inputxf >=50
set @retrunstr=‘取得学分‘
else
set @retrunstr=‘未取得学分‘
return @retrunstr
END
使用函数的代码如下：
SELECT 学号,成绩,dbo.xuefen(成绩) --要在使用的时候指明函数的所有者，在本例中，必须加上dbo.
AS 学分情况 FROM 课程注册 WHERE 课程号=‘0003‘
GO
二：表值函数：
表值函数遵循的原则：
1、RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。
2、function_body 不由BEGIN和END分隔。
3、RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。内嵌表值函数中使用的SELECT语句受到与视图中使用的SELECT语句相同的限制。
例：在STUDENT库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：
CREATE FUNCTION XUESHENG(@inputxbdm nvarchar(4)) RETURNS table
AS
RETURN
( SELECT 学号, 姓名, 入学时间 FROM 学生 WHERE 系部代码=@inputxbdm)
GO
建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：
SELECT * FROM DBO.XUESHENG(‘01‘)
GO
三、多语句表值函数
例：在STUDENT库中创建一个多语句表值函数CHENGJI，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。其代码如下：
CREATE FUNCTION CHENGJI( @inputkc as char(20) )
RETURNS @chji TABLE --@chji为表变量
( 课程名 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
RETURN --直接单独用 RETURN 即可，将表变量的数据返回
END
GO
在查询分析器中输入以下查询命令：
SELECT * FROM DBO.CHENGJIi(‘大学语文‘)
总结：
多语句函数的主体中允许使用以下语句。
1、赋值语句。
2、控制流语句。
3、DECLARE 语句，该语句定义函数局部的数据变量和游标。
4、SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。
6、INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。
7、EXECUTE语句调用扩展存储过程。
SqlServer 用户自定义函数