SQLserver自定义域FUNCTION的操作

前言：

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

1.定义：

自定义函数的类型：

1，标量值函数(返回标量值)

2，表值函数(内联表值函数，多语句表值函数，返回一个返回多个值的结果集)

2.三种定制功能的异同。

1、同点：

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

2、异点：

a.标量值函数返回数据类型值，
内联表值函数返回一个table，而多语句表值函数返回一个table变量(类似于前两个变量的组合)；
b.语法结构：标量值函数和多语句表值函数都是必需的。begin.........................end，内联表值函数不需要；
c.调用：要写入的标量函数。dbo.function_name；

3.函数参数

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

1输入：必须输入值。

2，可选值：执行该参数时，可选择不录入该参数。

3，缺省值：函数中存在缺省值，调用时不能指定。

4.示例：

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(20180808) name;
--select * from test_ceshi;

测试此自定义函数：func_date_get_name ，你可以得到。name结果是：Test1

以下是test_ceshi表的全部数据量。

示例2：此函数使用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：出处--海盗船长 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

5.修改和删除自定义函数。

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语句块，ISreturn。

内联表值函数：

1.      只能返回table，所以returns后背一定是TABLE

2.      AS后没有begin/end，只有一个return语句返回特定记录。

多条语句表值函数：

1.      returns返回的表类型将在后面直接定义。第一种是定义表名，表示表名将在前面添加。@，后跟关键字TABLE，最后是表的结构。

2.      在begin/end语句块，则需要直接返回的结果insert到returns最后，定义的表很好。return返回结果。

3.      最后，您需要做的就是return，return不遵循任何变量。