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进行更新的操作:
下一篇: C语言的引用传递方法