你的位置:首页 > 数据库

[数据库]13、SQL Server 自定义函数


SQL Server 自定义函数

在SQL Server中不仅可以使用系统函数(如:聚合函数,字符串函数,时间日期函数等)还可以根据需要自定义函数。

自定义函数分为标量值函数和表值函数。

其中,标量值函数用于返回单个值,而表值函数用于返回一个结果集。

函数参数

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

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

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

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

创建标量值函数

语法:

Create function 函数名(参数)Returns 返回值数据类型[with {Encryption | Schemabinding }][as]begin  SQL语句(必须有return 变量或值)EndSchemabinding :将函数绑定到它引用的对象上(注:函数一旦绑定,则不能删除、修改,除非删除绑定)

例子:

drop function dbo.input --删除函数gocreate function dbo.input --定义函数 架构.方法名(@num1 int,  --输入参数@num2 int = null, --可选参数@oper varchar = '+' --默认参数)returns intasbegin  declare @sum int  if(@oper='+')  begin    set @sum = @num1 + @num2  end  else  begin    set @sum = 0  end  return @sumendgoselect dbo.input(1,null,default) --参1必填,参2可选,参3默认select dbo.input(1,2,default) --输出3select dbo.input(1,2,'*') --输出0 *没判断

自定义函数可以将值放在局部变量中,用set select exec赋值

declare @number intselect @number = dbo.input(1,2,default)print @numberdeclare @set intset @set = dbo.input(1,2,default)print @setdeclare @exec intexec @exec = dbo.input 1,2,'+'print @exec

在查询中引用函数

create table test(  id int identity(1,1),  name varchar(10),  birthDay datetime)insert into test values('张三','1998-02-01'),('李四','1981-10-1'),('王五','1985-5-2')select * from test --测试信息

创建函数并执行后

create function dbo.getAge(@birthDay datetime)returns intasbegindeclare @age intset @age = datediff(yy,@birthDay,getdate())return @ageendselect name as 姓名,dbo.getAge(birthDay) as 年龄 from test

注意:标量值函数不可以返回文本(text、ntext)、图像、游标或时间戳类型的数据,并且不能用来修改数据库状态。

在select语句中使用函数可能会带来负面影响,因为每返回一行都会调用函数一次。所以在返回大型数据集时应该格外避免使用复杂的函数。

表值函数

表值函数包含两种类型:内联函数和多语句函数。

内联表值函数仅返回一个结果集,而多语句函数可以在函数体中包含一些控制逻辑。

1、内联表值函数

语法:

create function 函数名(参数)returns table[with {Encryption | Schemabinding }]as
return(一条SQL语句)

例子:

--创建create function getDetails(@id int)returns tableasreturn(select * from student where id = @id) --执行一条语句后返回--调用select * from dbo.getDetails(10)

2、多语句函数

多语句函数可以通过多条语句来创建临时表,具体需要哪些字段,以及符合哪些要求的数据被添加到临时表中等。

语法:

create function 函数名(参数)returns 表格变量名 table (表格变量定义)[with {Encryption | Schemabinding }]asbegin  SQL语句end 

例子:

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

可以看出,多语句函数返回结果是定义好表结构的虚拟表,最后有一个return用来告诉sql多语句已经执行完毕。不写会返回不了。