SQL server 自定义函数FUNCTION的使用

SQL server 同时被 2 个专栏收录
37 篇文章 3 订阅
53 篇文章 0 订阅

前言:

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

一、定义:

用户自定义函数的类型:

1、标量值函数(返回一个标量值)

2、表格值函数(内联表格值函数、多语句表值函数,返回一个结果集即返回多个值)

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

1、同点:

创建定义相同:         CREATE FUNCTION F_NAME(传入的参数名称    传入参数的类型)                                
                                  RETURNS         返回值类型                                 
                                  AS 

2、异点:

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

三、函数参数

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

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

拿个具体的例子说事:

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

https://mp.csdn.net/postedit/81702708

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

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('20180808') 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('20180808') ;

得到如下的结果:

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(20180808);

测试函数dbo.func_date_get_table_test(),结果如下图:

例子2:出处--海盗船长  https://www.cnblogs.com/baidawei/p/4732969.html

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

 

 

©️2021 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值