13、SQL Server 自定义函数

SQL Server 自定义函数

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

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

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

函数参数

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

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

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

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

创建标量值函数

语法:

Create function 函数名(参数)
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
  SQL语句(必须有return 变量或值)
End

Schemabinding :将函数绑定到它引用的对象上(注:函数一旦绑定,则不能删除、修改,除非删除绑定)

例子:

drop function dbo.input  --删除函数
go
create function dbo.input --定义函数 架构.方法名
(
@num1 int,   --输入参数
@num2 int = null,  --可选参数
@oper varchar = '+' --默认参数
)
returns int
as
begin
    declare @sum int
    if(@oper='+')
    begin
        set @sum = @num1 + @num2
    end
    else
    begin
        set @sum = 0
    end
    return @sum
end
go

select dbo.input(1,null,default)  --参1必填,参2可选,参3默认

select dbo.input(1,2,default) --输出3

select dbo.input(1,2,'*') --输出0 *没判断

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

declare @number int
select @number = dbo.input(1,2,default)
print @number

declare @set int
set @set = dbo.input(1,2,default)
print @set

declare @exec int
exec @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 int
as
begin
declare @age int
set @age = datediff(yy,@birthDay,getdate())
return @age
end

select 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 table
as
return(select * from student where id = @id) --执行一条语句后返回

--调用
select * from dbo.getDetails(10)

2、多语句函数

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

语法:

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

例子:

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

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

posted @ 2015-08-15 22:55  海盗船长  阅读(3422)  评论(0编辑  收藏  举报