Sqlserver 自定义函数 Function使用介绍

一.function:
在sqlserver2008中有3中自定义函数：标量函数/内联表值函数/多语句表值函数，首先总结下他们语法的异同点： 

同点：
1.创建定义是一样的： a, create function f_name(传入的参数名称 传入参数的类型) b,returns 返回值类型 c,as 异点：1.标量函数返回的是一个数据类型值，内联表值函数返回的是一个table，而多语句返回的是一个table的变量（类似前面两个的结合）；
2.语法的结构：标量函数和多语句函数都是要有begin，，，end，内联表值函数就没有；
3.调用：标量函数要写成在dbo,function_name；

标量函数，利用上篇文章写的数据表，在[t_员工信息]表中查姓名为李异峰员工的工号：

use sql_system 
go
create function f_gonghao(@xingming nvarchar(5))
returns int 
as
begin
declare @gonghao int
set @gonghao =(select y.工号 from[t_员工信息] as y where y.姓名 =@xingming )
return @gonghao
end
go
/*上面是标量函数的一个简单的例举，下面就开始调用，注意是在dbo.下*/
select [姓名] from[t_员工信息] where [姓名]='李异峰' /**对比的查询*/
/*请注意观察，和对比*/
select [姓名],dbo.f_gonghao ('李异峰')as 工号 from[t_员工信息] where [姓名]='李异峰'
go

f5下：

内联表值函数：他返回的可是一个table哦，比如：筛选出2014.2.28号所有员工的打卡记录：

use sql_system 
go
create function f_dakajilu(@riqi date)
returns table
as
return(
select* from[t_考勤]as k where k.日期 =@riqi 
)
go
/*需要注意的就是他的写法上没有begin+end*/
/*下面就是将date带入函数中*/
select*
from [f_dakajilu]('2014/02/28')
go

f5：

多语句表值函数就是，表值函数+内联表值函数综合版：就写个返回当天打卡记录和涉及员工的个人信息：

use sql_system 
go
create function f_d_dakajilu(@riqi date)
returns @temp_table table( /*这里要注意的就是既然是多语句的话，那你就要告诉计算机你要查询的列是哪些*/
[姓名]nvarchar(5) not null,
[工号]int not null,
[职位]nvarchar(10) not null,
[部门]nvarchar(5) not null,
[是否夜班]nchar(1) not null,
[日期]date not null,
[当天上班时间]float(1)not null
) 
/*以上就是要告诉计算机你要的一个基本信息列*/
as
begin
insert into @temp_table /*这句的意思是将下面的查询结果插入到@temp_table变量中去*/
select y.姓名 ,y.工号 ,y.职位 ,y.部门 ,k.夜班 ,k.日期 ,k.当天上班时间 
from[t_员工信息] as y cross join [t_考勤] as k  /*这里我用的是交叉连接*/
where y.工号 =k.工号 and k.日期=@riqi 
return 
end
go
select*
from[f_d_dakajilu]('2014/02/28')
go

f5：

二：cursor，当要检索复杂的数据的每条数据的时候就可以用到它，类似于c语言的指针一样，他可以在你的数据表中阅历每条数据和更新。 1.新写个cursor阅历数据表(t_员工信息)的数据过程： 1.1.声明一个只读cursor：

use sql_system 
set transaction isolation level repeatable read
begin transaction cursor_read_t_员工信息
declare cur_t_员工信息 scroll cursor for
select y.姓名 ,y.工号 ,y.职位 ,y.部门 from[t_员工信息]as y order by y.工号 asc 
commit transaction cursor_read_t_员工信息
go

1.2.打开：

open global cur_t_员工信息
go

1.3.阅历每条数据：

/*声明4个变量用来接收暂存游标中的数据*/
declare @xingming nvarchar(3),@gonghao int,@zhiwei nvarchar(10),@bumen nvarchar(8)
/*利用全局变量求取当前数据表中的数据行数和*/
print'当前数据表中有'+cast(@@cursor_rows as nvarchar(6))+'行数据。'
/*读取第一条数据并存入暂存变量中*/
fetch first from[cur_t_员工信息]into @xingming,@gonghao,@zhiwei,@bumen
/*利用@@fetch_status返回的数值来确定cursor在数据表中读取的情况=0为fetch执行成功*/
while(@@fetch_status =0)
begin
print'姓名：'+@xingming+' 工号：'+convert(varchar(3),@gonghao)+' 职位：'+@zhiwei+' 部门：'+@bumen
fetch next from[cur_t_员工信息] into @xingming,@gonghao,@zhiwei,@bumen
end
go

f5:

1.4.用完后就关闭和释放：

/*关闭*/
close global cur_t_员工信息
/*释放内存*/
deallocate global cur_t_员工信息
go

这样的话就实现了读取每条记录的功能，然后就可以用它来更新相关的数据条（所有的工号值+100）：

use sql_system 
/*下面声明和打开update——cursor*/
set transaction isolation level repeatable read
begin transaction tr_update_yg 
declare cur_t_yg cursor for
select yg.姓名 ,yg.工号 ,yg.职位 ,yg.部门 from[t_yuangongxinxi]as yg for 
update 
open global cur_t_yg
commit transaction tr_update_yg

update:

print'当前有'+convert(varchar(3),@@cursor_rows)+'条数据行。'
fetch next from[cur_t_yg]
while(@@fetch_status=0)
begin
update[t_yuangongxinxi] 
set[工号] =[工号]+100
where current of cur_t_yg
fetch next from[cur_t_yg] 
end

select:

关闭释放：

close global cur_t_yg
deallocate global cur_t_yg

三：procedure,存储过程是利用sql server所提供的transact-sql语言所编写的程序,同时也能在高级语言上调用其存储过程。 3.1.无参数：说一个调用查询数据表的pro：

use sql_system 
go
create procedure pro_select_t
with recompile 
as
select* from[t_yuangongxinxi] 
go
execute:
execute pro_select_t

f5：

3.1.2.execute pro_select_t在这里其实就是一个view，还可以把execute调用结果插入到新表：

use sql_system 
select*
into aa 
from[t_yuangongxinxi] 
go
truncate table aa --truncate与delete的区别就是tr效率高于de，而且de清空时会在日志里面留下恢复记录
go
insert into aa
execute pro_select_t
go

结果就不截图了， 3.2.带参pro，写个修改后输出修改的信息： 例如公司的员工经常有职位变动的情况，就写个这方面的，给出变动员工的工号就可以修改该员工的职位，然后将修改的操作反馈出来：

use sql_system 
go
create procedure pro_daup_zhiwei
@gonghao int,@zhiwei nvarchar(10),@returns nvarchar(50) output
as
begin
/*获取更新前的职位信息*/
declare @qian_return_zhiwei nvarchar(10),@xingming nvarchar(3)
select @qian_return_zhiwei =aa.职位 ,@xingming =aa.姓名 
from[aa]
where aa.工号 =@gonghao 
/*更新*/
update[aa] 
set[职位] =@zhiwei where aa.工号 =@gonghao 
set @returns = '已经成功将工号为：【'+convert(varchar(3),@gonghao)+'】,姓名为：【'+@xingming+'】,职位【'+
@qian_return_zhiwei+'】更新为：【'+@zhiwei
end

execute:

declare @printf nvarchar(50);
execute pro_daup_zhiwei 101,'sql工程师',@printf output 
select @printf as '更新消息'
go

f5:

pro就说到这里了；
四：trigger，触发器，类似于一个地雷一样，只要你触犯了他的要求的话，他就开始工作了，也可以利用这个功能去维持或阻挡某些不想发生的错误操作， ddl：如：不许删除某个【aa】表：

use sql_system 
go
create trigger drop_t
on database for drop_table 
as
begin
rollback transaction 
print'不能删除的,因为我已经添加了触发保护！'
end

drop：

drop table aa 

f5：

dml，是解决对最低层数据的问题： 在这里就存在临时的2个表：deleted 和 inserted 逻辑（概念）表，要搞明白就要知道trigger的工作原理： insert 操作时：

delete 操作时：

update 操作时：

简单举个例子来说下update的操作：也就是重点看inserted和deleted表上：

use [sql_system]
go
/****** object: trigger [dbo].[update_t] script date: 03/04/2014 16:04:21 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create trigger [dbo].[update_t]
on [dbo].[aa] instead of update
as
begin
insert into t_update_hou
select* 
from[inserted] 
/*将更新成的数据插入到【t_update_hou】中*/
insert into t_update_qian
select*
from[deleted] 
/*将更新前的数据插入到【t_update_qian】中*/
print'更新完毕，更新前的数据已经写入到【t_update_qian】，更新后的数据插入到【t_update_hou】。'
end

update：

use sql_system 
go
update[aa] 
set aa.职位 ='sql高级工程师'
where aa.工号=101
/*以上是个简单的更新操作，用于update触发*/
select* from[t_update_hou] --修改成的数据
select* from[t_update_qian] --待修改的数据
go

f5：

在去年的的时候我写了一个利用trigger对多表连接的view进行更新的操作：