SQL之用户自定义函数FUNCTION

类型

用户自定义函数的类型：

1、标量值函数（返回一个标量值）

2、表格值函数（内联表格值函数、多语句表值函数，返回一个结果集即返回多个值）

三种自定函数的异同点

1、同点：

创建定义相同：

CREATE FUNCTION F_NAME(传入的参数名称   传入参数的类型)                 
RETURNS 返回值类型                 
AS 
	...

2、异点：

a.标量值函数返回的是一个数据类型值，
内联表值函数返回的是一个table，而多语句表值函数返回的是一个table的变量（类似前面两个的结合）。
b.语法的结构：标量值函数和多语句表值函数都是要有begin…end，内联表值函数就没有。
c.调用：标量函数要写成在dbo.function_name。

函数参数

参数可以是常量、表中的某个列、表达式或其他类型的值。在函数中有三种类型的参数。

1、输入：指必须输入一个值。

2、可选值：在执行该参数时，可以选择不输入参数。

3、默认值：函数中默认有值存在，调用时可以不指定该值。

应用

标量值函数

基本格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
	function_body --函数体(即Transact-SQL语句)
	RETURN  表达式;
END

如：创建一个返回标量值的用户定义函数 RectangleArea：输入矩形的长和宽就能计算矩形的面积。

create function RectangleArea(@x int, @y int)
returns int
as
begin
	return @x * @y
end;
go
declare @area int
exec @area = RectangleArea 2, 3
print '矩形的面积等于' + str(@area);

内联表格值函数

特点：内联表格值函数支持在WHERE子句中使用参数

基本格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS table    --返回一个表
[WITH ENCRYPTION]  --如果指定了encryption，则函数被加密
[AS]
	RETURN （一条SQL语句）

如：创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。

create function create_table()
returns table
as
return (
select c.cno, c.cname, count(sy.sno) num, max(sy.score) maxScore, min(sy.score) minScore, avg(sy.score) avgScore
from course c, study sy
where c.cno = sy.cno
group by c.cno, c.cname
);
go
select * from create_table();

多语句表值函数

多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是Table类型。多语句表值函数通过多条语句来创建Table类型的数据。这里不同于内联表值函数，内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数，则是需要指定具体的Table类型的结构。也就是说返回的Table已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。

基本格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS @Table_Variable_Name table (Column_1 culumn_type,Column_2 culumn_type)    
--RETURNS @表变量 table 表的定义(即列的定义和约束)
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
	函数体(即 Transact-SQL 语句)
	RETURN  
END

如：

CREATE FUNCTION dbo.func_date_get_table_test(@date_into varchar(8)) 
RETURNS @table_test table(
    date varchar(8),
    ID varchar(20),
    name varchar(20)
)
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
as
begin
	insert into @table_test select statdate, value_id, Value_name from test_ceshi where statdate = @date_into
	RETURN 
end;
go
select * from dbo.func_date_get_table_test(20180808);

或者：

create function dbo.Test()
returns @temp table (
    name varchar(20),
    sex char(2),
    age int
)
as
begin
	insert into @temp (name,sex,age) values ('多语句','嘛',18)
	insert into @temp (name,sex,age) select name,sex,age from student where age > 18
	return 
end

删除和修改自定义函数

删除基本格式：

drop function func_date_get_name

修改基本格式：

alter function 函数名（参数）
returns table
as
	return(一条SQL语句)

注意

在编写自定义函数时需要注意的：

标量函数：

1. 所有的入参前都必须加@

2. create后的返回，单词是returns，而不是return

3. returns后面的跟的不是变量，而是返回值的类型，如：int，char等。

4. 在begin/end语句块中，是return。

内联表格值函数：

1. 只能返回table，所以returns后面一定是TABLE
2. AS后没有begin/end，只有一个return语句来返回特定的记录。

多语句表值函数：

1. returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。

2. 在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

3. 最后只需要return，return后面不跟任何变量。