关于值班管理的存储过程

2024-07-21 02:05:42

字体：大中小

来源：转载

供稿：网友

一个bt主任的要求值班管理要求如下

1 一组队列 n 个人，有4种角色，领导，汉子，大妈，司机。n个人根据自己角色按顺序排好队

2 值班要求：周一到周日 1个领导值班1个司机值班；周一到周日每晚1个汉子值班；周六周日上午下午2个大妈值班；假期每天1个领导1个司机1个汉子上午下午2个大妈

3 要求队列可增删查该，人员顺序可以调整，队列发生变化时，值班表自动更新

4 要求队列人员随时可以抽调对列中的人员不参加本轮排序（出差或请假）下轮继续按队列顺序排序，人员抽调后，队列自动向前顶替

5 换班等...

建2个表

1 watching

    [datetime] 日期 [weekday]星期 [leaderid]领导id [maleid]汉子id [female1]大妈1id [female2]大妈2id  [driverid] 司机id [mark]备注

2 watching_person

   [ordercode]人员编号 [personid] 人员id [part]人员角色 [leave]是否离开 [mark]备注

part 为人员角色 1领导2汉子3大妈4司机

当新的队列产生时需要更细从明天以后的值班安排表（此处为30天），然后将按角色排队好的起始位置传给存储过程（即领到从第几位开始排司机从第几位开始排汉子大妈...）

create proc proc_watchingsetup --参数为四种角色的起始位置 @leader int, @male int, @female int, @driver intas

declare @i int                                           --计数器declare @j intdeclare @personid intdeclare @weekday intdeclare @insertpoint datetimedeclare @msg char(20)

set @i=1set @j=1

-- 事务开始begin tran rechange--删除明天以后的记录（队列已改变删除以前的）delete from watching where [datetime]>getdate()

if (@@error <>0)    begin      rollback tran     set @msg='error1'     return   end

--重新插入后30天的日期和星期while @i<=30 begin     insert watching (datetime,weekday) values (dateadd(day,@i,{fn curdate()}),datepart(weekday,dateadd(day,@i,{fn curdate()})))          set @[email protected]+1 end

if (@@error !=0)    begin      rollback tran     set @msg='error2'     return   end

--开始使用游标

set @j=1

--////首先按排队顺序读出领导的队列

declare cur_watchingperson scroll cursor for   select personid from watching_person where part=1 order by ordercode asc

open cur_watchingperson

--移动到开始位置fetch absolute @leader from cur_watchingperson into @personidif @@fetch_status=-1   fetch first from cur_watchingperson into @personid

set @i=1

while @i<=30 begin

     while @j<=7 --最长可能是1人插入7天       begin                update watching set [email protected] where [datetime]=(dateadd(day,@i,{fn curdate()}))                if (@@error !=0)           begin            rollback tran           set @msg='error3'          return        end        --如果不足7天就到周末退出循环换人        select @weekday=datepart(weekday,dateadd(day,@i,{fn curdate()}))        set @[email protected]+1        if (@weekday=1)          break                 end

     set @j=1          fetch next from cur_watchingperson into @personid     -- 如果超出边界回头队列第一位     if @@fetch_status=-1        fetch first from cur_watchingperson into @personid end

close cur_watchingpersondeallocate cur_watchingperson

--////////////司机很领导完全一样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 @personidif @@fetch_status=-1    fetch first from cur_watchingperson4 into @personid

set @i=1

while @i<=30 begin     while @j<=7 --最长可能是1人插入7天       begin                update watching set [email protected] 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 @[email protected]+1        if (@weekday=1)          break                 end

     set @j=1     fetch next from cur_watchingperson4 into @personid     -- 如果超出边界回头队列第一位     if @@fetch_status=-1        fetch first from cur_watchingperson4 into @personid end

close cur_watchingperson4deallocate cur_watchingperson4

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

--汉子每天1人值夜班相对容易declare cur_watchingperson2 scroll cursor for   select personid from watching_person where part=2 order by ordercode asc

open cur_watchingperson2

--移动到开始位置fetch absolute @male from cur_watchingperson2 into @personidif @@fetch_status=-1    fetch first from cur_watchingperson2 into @personid

set @i=1

while @i<=30 begin      update watching set [email protected] where [datetime]=(dateadd(day,@i,{fn curdate()}))           if (@@error !=0)        begin           rollback tran          set @msg='error3'          return       end             set @[email protected]+1

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

close cur_watchingperson2deallocate 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 @personidif @@fetch_status=-1    fetch first from cur_watchingperson3 into @personid

set @i=1

while @i<=30 begin

     select @weekday=[weekday] from watching where [datetime]=(dateadd(day,@i,{fn curdate()}))           --判断只有周末的半天才值班安排2人     if @weekday=7 or @weekday=1        begin                       --插入第一位        update watching set [email protected] where [datetime]=(dateadd(day,@i,{fn curdate()}))                     if (@@error !=0)          begin            rollback tran           set @msg='error3'          return        end

        fetch next from cur_watchingperson3 into @personid        -- 如果超出边界回头队列第一位        if @@fetch_status=-1               fetch first from cur_watchingperson3 into @personid                --插入第二位        update watching set [email protected] where [datetime]=(dateadd(day,@i,{fn curdate()}))                     if (@@error !=0)          begin           --rollback tran           set @msg='error3'          return        end

       end             set @[email protected]+1      fetch next from cur_watchingperson3 into @personid        -- 如果超出边界回头队列第一位        if @@fetch_status=-1               fetch first from cur_watchingperson3 into @personid        end

close cur_watchingperson3deallocate cur_watchingperson3

commit tran

以上为队列改变时生成新值班安排的存储过程

其他诸如规定假期调整人员大同小异欢迎批评指正

上一篇：构件化软件生产带来的两场战争

下一篇：GROW THAT DBA CAREER