SqlServer——用户自定义函数
在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。可以使用CREATE FUNCTION 语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。
用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。
自定义函数分为 标量函数、表值函数、多语句表值函数 三种。
（1）标量函数：对单一值操作，返回单一值。在begin…end块中定义函数主体。只要在能够使用表达式的地方，就可以使用标量函数。
（2）表值函数：返回值是一个记录集合——表。在此函数中，无begin…end块中定义函数主体，只有 return 语句包含一条单独的select语句。
（3）多语句表值函数：返回值是一个记录集合——表。返回值是由选择的结果构成的记录集。
区别：内嵌表值函数没有函数主体，返回的表是 return 单个select语句的结果集；而多语句表值函数在begin…end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表 中，最后返回表。
一：标量函数
格式如下：
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
declare @返回值 返回数据类型
sql语句
RETURN @返回值
END
例：1、先创建一个student表，如下：
create table dbo.student
(
name varchar(20),sex varchar(10),nianling int
)
插入数据：
insert into dbo.student (name ,sex ,nianling)values('小明','男',24),
('小李','男',24),('小虎','男',24),('小强','男',24),('小龙','男',24)
2、创建student_cj表，并插入数据。
create table student_cj
(
name varchar(20) not null ,
km varchar(10) not null ,
cj int not null
)
insert into student_cj values('小明','数学',90),('小明','语文',100),('小李','语文',100),('小虎','语文',100),('小强','语文',100),
('小龙','语文',100),('小李','数学',90),('小虎','数学',90),('小强','数学',100),('小龙','数学',100)
3、创建函数，如下：
Create function score(@name varchar(20),@km varchar(10))
returns int --返回类型为 int
AS
begin
declare @CJ int --声明返回变量
select @CJ =cj from student_cj where name =@name and @km =km
return @CJ
end
4、使用函数，如下：
select name,dbo.score(name ,'数学')as '成绩' from student --要在使用的时候指明函数的所有者，在本例中，必须加上dbo.
结果如下：
二：表值函数：
表值函数遵循的原则：
1、RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。
2、function_body 不由BEGIN和END分隔，因为只有 return 一条语句。
3、RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。内嵌表值函数中使用的SELECT语句受到与视图中使用的SELECT语句相同的限制。
例：1、创建函数如下：
Create function scoretable(@km varchar(10)) returns table --RETURNS子句仅包含关键字table
AS
return select student.name,student_cj.cj from student_cj inner join student on student_cj.name =student.name where student_cj.km =@km
2、建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：
select * from dbo.scoretable('数学')
结果：
三、多语句表值函数
例：
1、创建函数如下：
create function scoretables(@km varchar(10))
returns @scoretables table --创建表变量
(
name varchar(20),sex varchar(10),nianling int,km varchar(10),cj int
)
as
begin
insert into @scoretables
select student.name,student.sex ,student.nianling ,student_cj.km , student_cj.cj from student_cj inner join student on student_cj.name =student.name
where student_cj.km =@km
return --直接单独用 RETURN 即可，将表变量的数据返回
end
2、使用函数
select * from scoretables('数学')
总结：
多语句函数的主体中允许使用以下语句。
1、赋值语句。
2、控制流语句。
3、DECLARE 语句，该语句定义函数局部的数据变量和游标。
4、SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。
6、INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。
7、EXECUTE语句调用扩展存储过程。
