sql Cursor
表A
表B
现在希望得到这样的数据表A里的O_Saloary加上表B的A_Salary是当前的总工资,并更新到表A。
你可以用视图来,表连接下就可以。
现在我们考虑下让SQL自己来处理,游标就是很好的方法。
原理:游标就是把数据按照指定要求提取出相应的数据集,然后逐条进行数据处理。
1)允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作,而不是对整个行集合执行同一个操作。
2)提供对基于游标位置的表中的行进行删除和更新的能力。
3)游标实际上作为面向集合的数据库管理系统(RDBMS)和面向行的程序设计之间的桥梁,使这两种处理方式通过游标沟通起来。
参数说明:
列表中的各个变量从左到右与游标结果集中的相应列相关联。
各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。
declare @id nvarchar(20) --定义变量来保存ID号
declare @A float --定义变量来保存值
declare mycursor cursor for select * from tb_c --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @id,@A --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
select tb_b.name,(tb_b.gz + @A) from tb_b where tb_b.id = @id --开始做想做的事(什么更新呀,删除呀)
fetch next from mycursor into @id,@A --跳到下一条数据
end
close mycursor --关闭游标
deallocate mycursor --删除游标
我写的第一个游标 加班时间转入
--sp_helptext Sal_wotinto_insert
--HR_WotInto_qry '2008-08-01','2008-08-31','8'
CREATE procedure Sal_wotInto_insert
@starttime as datetime=null,
@endtime as datetime=null,
@PerSn as varchar(10)=null,
@uid as varchar(10)
as
declare @Work_type as varchar(10)--EF016B003
declare @wot_date as smalldatetime--EF016B004
declare @S_time as datetime--EF016B006
declare @E_time as datetime--EF016B007
declare @eat_time as varchar(10)--EF016B008
declare @empno as varchar(10)--EF016B009
declare @alltime as nvarchar(10)--EF016B010
declare @formno as varchar(10)--EF016B001
declare @sheetno as varchar(10)--EF016B002
declare @Ot_in as varchar(10)
declare @Ot_out as varchar(10)
declare @tlen as varchar(10)
declare selwot Cursor for
-- HR_WotInto_qry @starttime,@endtime,@PerSn
select EF016B003, EF016B004, EF016B006,EF016B007,EF016B008,EF016B009,EF016B010 , EF016B001,EF016B002
--,Ot_in,Ot_out ,
,dbo.fun_att_get_ottime(EF016B009, EF016B004, 'i') as Ot_in ,dbo.fun_att_get_ottime(EF016B009, EF016B004, 'o') as Ot_out,
--dbo.fun_att_wot_realtime(EF016B006, EF016B007, ot_in, ot_out) as tlen
--實際加班時間減去用餐時間
(cast(dbo.fun_att_wot_realtime(ef016b009, ef016b004, EF016B006, EF016B007) as float) -cast(EF016B008 as float) / cast(60 as float) )as tlen
from EF000A,EF016B,HR_ATTdaily h
where EF016B009=h.empno and EF016B004=h.D_date and EF016B004 between @starttime and @endtime and EF016B001=EF000A02 and EF016B002 = EF000A03 and EF000A04='5' and EF000A014='1' and EF016B009 IN ( SELECT EMPNO FROM dbo.fun_sal_operation_emplist(@PerSn) )
open selwot
fetch next from selwot into @Work_type,@wot_date,@S_time,@E_time,@eat_time,@empno,@alltime,@formno,@sheetno,@Ot_in,@Ot_out,@tlen
while @@fetch_status=0
begin --select * from Sal_ot_p3
declare @Ot_indate varchar(20)
declare @Ot_outdate varchar(20)
set @Ot_in=convert(varchar(10),@wot_date,120)+' '+@Ot_in
set @Ot_out=convert(varchar(10),@wot_date,120)+' '+@Ot_out
declare @rstime varchar(20)
declare @retime varchar(20)
select @rstime=abn_in from HR_ATTabn where Empno=@empno and Abn_reason='5' and D_date=@wot_date
select @retime=abn_out from HR_ATTabn where Empno=@empno and Abn_reason='5' and D_date=@wot_date
declare @PNO as varchar(10)
select @PNO= Per_SNNO from Sal_control where Per_SNNO is not null and (SAL25 is null or SAL25='')
if(@rstime is null and @rstime ='')
exec Sal_ot_p3_save @PNO,@empno,@wot_date,@S_time,@E_time,@Ot_in,@Ot_out,@eat_time,@tlen,@uid,@Work_type,@formno,@sheetno
else
exec Sal_ot_p3_save @PNO,@empno,@wot_date,@S_time,@E_time,@rstime,@retime,@eat_time,@tlen,@uid,@Work_type,@formno,@sheetno
fetch next from selwot into @Work_type,@wot_date,@S_time,@E_time,@eat_time,@empno,@alltime,@formno,@sheetno,@Ot_in,@Ot_out,@tlen
end
close selwot
deallocate selwot
GO