SQL server 自定义函数FUNCTION的使用

前言：

        在SQL server中不仅可以可以使用系统自带的函数（时间函数、聚合函数、字符串函数等等），还可以根据需要自定义函数。

一、定义：

用户自定义函数的类型：

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

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

二、三种自定函数的异同点

1、同点：

2、异点：

三、函数参数

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

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

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

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

四、举例说明：

1、标量值函数定义格式：

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

拿个具体的例子说事：

准备数据：之前有一篇博文写了新建表和插入数据的语句，可参考：

CSDN

想要输入时间得到名字的函数

CREATE FUNCTION dbo.func_date_get_name(@date_into varchar(8)) --CREATE FUNCTION 函数名称（@参数名 参数的数据类型） RETURNS varchar(20) --返回返回值的数据类型 --[WITH ENCRYPTION] --如果指定了 encryption 则函数被加密 as BEGIN declare @result_name varchar(20) select @result_name = Value_name from test_ceshi where statdate = @date_into RETURN @result_name END --select dbo.func_date_get_name('') name; --select * from test_ceshi;

测试这个自定义函数：func_date_get_name ，即可得到name的结果为：Test1

以下是test_ceshi表的全量数据。

例子二：这个函数使用了if…else条件语句

--编写一个函数，该函数，可以通过输入借书时间来判断是否到期，当借阅时间大于30天，返回已经过期；否则返回还未到期。 CREATE FUNCTION IsDateout(@BDate datetime) returns nvarchar(20) AS BEGIN     DECLARE @myresult nvarchar(20)     IF (datediff(day,@BDate,getdate())>30)     BEGIN     SET @myresult='已过期'     end     else         begin     set @myresult='未到期'     end     RETURN (@myresult) END SELECT dbo.IsDateout(cast('2018-01-01' AS datetime))--结果已过期 SELECT dbo.IsDateout(cast('2018-08-01' AS datetime))--结果未到期

2、内联表格值函数定义格式：

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

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

有了格式，写个实例：

CREATE FUNCTION dbo.func_date_get_table(@date_into varchar(8)) RETURNS table --[WITH ENCRYPTION] --如果指定了 encryption 则函数被加密 as RETURN select statdate,Value_name from test_ceshi where statdate = @date_into --select * from dbo.func_date_get_table('') ;

得到如下的结果：

3、多语句表值函数定义格式：

       多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是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

因为此类型的自定义函数在实际工作中使用最多，我多举几个例子说明，有些是别人写的内容：

例子1：

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 @table_test select statdate,value_id,Value_name from test_ceshi where statdate = @date_into RETURN end --select * from dbo.func_date_get_table_test(); 

测试函数dbo.func_date_get_table_test()，结果如下图：

例子2：出处–海盗船长  13、SQL Server 自定义函数 – 海盗船长 – 博客园

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

五、修改和删除自定义函数

1、使用alter语句修改自定义函数：

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

2、使用drop语句删除：

drop function func_date_get_name

六、注意事项：

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

标量函数：

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后面不跟任何变量。