关于值班管理的数据库存储过程

--////////////司机很领导完全一样
declare cur_watchingPerson4 scroll cursor For
select Personid from watching_person where part=4 order by orderCode asc 织梦好，好织梦

open cur_watchingPerson4

织梦内容管理系统

--移动到开始位置
fetch absolute @driver from cur_watchingPerson4 into @PersonID
if @@fetch_status=-1
fetch first from cur_watchingperson4 into @PersonID dedecms.com

set @i=1 内容来自dedecms

while @i<=30
begin
while @j<=7 --最长可能是1人插入7天
begin
update watching set driverId=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))
if (@@error !=0)
Begin
--rollback tran
set @msg='error3'
return
end

select @weekday=datepart(weekday,dateadd(day,@i,{fn curdate()}))
set @i=@i 1
if (@weekday=1)
break

end copyright dedecms

set @j=1
fetch next from cur_watchingperson4 into @PersonID
-- 假如超出边界 回头队列第一位
if @@fetch_status=-1
fetch first from cur_watchingperson4 into @PersonID
end 本文来自织梦

Close cur_watchingPerson4
deallocate cur_watchingPerson4

--///////////

--汉子天天1人值夜班 相对轻易
declare cur_watchingPerson2 scroll cursor For
select Personid from watching_person where part=2 order by orderCode asc

织梦好，好织梦

open cur_watchingPerson2 dedecms.com

--移动到开始位置
fetch absolute @male from cur_watchingPerson2 into @PersonID
if @@fetch_status=-1
fetch first from cur_watchingperson2 into @PersonID dedecms.com

set @i=1

while @i<=30
begin

update watching set MaleId=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))
if (@@error !=0)
Begin
rollback tran
set @msg='error3'
return
end

set @i=@i 1

copyright dedecms

fetch next from cur_watchingperson2 into @PersonID
-- 假如超出边界 回头队列第一位
if @@fetch_status=-1
fetch first from cur_watchingperson2 into @PersonID

end 本文来自织梦

Close cur_watchingPerson2
deallocate cur_watchingPerson2

--大妈每周六周日2人值白班
declare cur_watchingPerson3 scroll cursor For
select Personid from watching_person where part=3 order by orderCode asc

open cur_watchingPerson3 织梦好，好织梦

fetch absolute @female from cur_watchingPerson3 into @PersonID
if @@fetch_status=-1
fetch first from cur_watchingperson3 into @PersonID

set @i=1 内容来自dedecms

while @i<=30
begin dedecms.com

select @weekday=[weekday] from watching where [datetime]=(dateadd(day,@i,{fn curdate()}))

--判定 只有周末的半天才值班 安排2人
if @weekday=7 or @weekday=1
begin

--插入第一位
update watching set Female1=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))

if (@@error !=0)
Begin
rollback tran
set @msg='error3'
return
end copyright dedecms

fetch next from cur_watchingperson3 into @PersonID
-- 假如超出边界 回头队列第一位
if @@fetch_status=-1
fetch first from cur_watchingperson3 into @PersonID
--插入第二位
update watching set Female2=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))

if (@@error !=0)

看完这篇，您有何感觉呢？