MSSQL网页设计关于值班办理的存储历程

EXAMPLE存储引擎是一个不做任何事情的存根引擎。它的目的是作为MySQL源代码中的一个例子，用来演示如何开始编写一个新存储引擎。同样，它的主要兴趣是对开发者。EXAMPLE存储引擎不支持编索引。存储历程
一个bt主任的请求值班办理请求以下

1一组行列n团体，有4种脚色，向导，男人，年夜妈，司机。n团体依据本人脚色按按次排好队

2值班请求：周一到周日1个向导值班1个司机值班；周一到周日每晚1个男人值班；周六周日上午下战书2个年夜妈值班；假期天天1个向导1个司机1个男人上午下战书2个年夜妈

3请求行列可增删查该，职员按次能够调剂，行列产生变更时，值班表主动更新

4请求行列职员随时能够抽调对列中的职员不列入本轮排序（出差或告假）下轮持续按行列按次排序，职员抽调后，行列主动向前顶替

5调班等...

建2个表

1watching

[datetime]日期[weekday]礼拜[leaderid]向导id[maleid]男人id[female1]年夜妈1id[female2]年夜妈2id[driverid]司机id[mark]备注

2watching_person

[ordercode]职员编号[personid]职员id[part]职员脚色[leave]是不是分开[mark]备注

part为职员脚色1向导2男人3年夜妈4司机

当新的行列发生时必要更细从今天今后的值班布置表（此处为30天），然后将按脚色列队好的肇端地位传给存储历程（即领到从第几位入手下手排司机从第几位入手下手排男人年夜妈...）

createprocProc_WatchingSetup--参数为四种脚色的肇端地位@leaderint,@Maleint,@Femaleint,@Driverintas

declare@iint--计数器declare@jintdeclare@PersonIDintdeclare@weekdayintdeclare@InsertPointdatetimedeclare@msgchar(20)

set@i=1set@j=1

--事件入手下手BegintranReChange--删除今天今后的纪录（行列已改动删除之前的）deletefromWatchingwhere[Datetime]>GetDate()

if(@@error0)Beginrollbacktranset@msg=error1returnend

--从头拔出后30天的日期和礼拜while@i<=30begininsertWatching(Datetime,WeekDay)values(dateadd(day,@i,{fncurdate()}),datepart(weekday,dateadd(day,@i,{fncurdate()})))set@i=@i+1end

if(@@error!=0)Beginrollbacktranset@msg=error2returnend

--入手下手利用游标

set@j=1

--////起首按列队按次读出向导的行列

declarecur_watchingPersonscrollcursorForselectPersonidfromwatching_personwherepart=1orderbyorderCodeasc

opencur_watchingPerson

--挪动到入手下手地位fetchabsolute@leaderfromcur_watchingPersoninto@PersonIDif@@fetch_status=-1fetchfirstfromcur_watchingpersoninto@PersonID

set@i=1

while@i<=30begin

while@j<=7--最长多是1人拔出7天beginupdatewatchingsetLeaderId=@PersonIDwhere[datetime]=(dateadd(day,@i,{fncurdate()}))if(@@error!=0)Beginrollbacktranset@msg=error3returnend--假如不敷7天就到周末加入轮回换人select@weekday=datepart(weekday,dateadd(day,@i,{fncurdate()}))set@i=@i+1if(@weekday=1)breakend

set@j=1fetchnextfromcur_watchingpersoninto@PersonID--假如超越界限转头行列第一名if@@fetch_status=-1fetchfirstfromcur_watchingpersoninto@PersonIDend

Closecur_watchingPersondeallocatecur_watchingPerson

--////////////司机很向导完整一样declarecur_watchingPerson4scrollcursorForselectPersonidfromwatching_personwherepart=4orderbyorderCodeasc

opencur_watchingPerson4

--挪动到入手下手地位fetchabsolute@driverfromcur_watchingPerson4into@PersonIDif@@fetch_status=-1fetchfirstfromcur_watchingperson4into@PersonID

set@i=1

while@i<=30beginwhile@j<=7--最长多是1人拔出7天beginupdatewatchingsetdriverId=@PersonIDwhere[datetime]=(dateadd(day,@i,{fncurdate()}))if(@@error!=0)Begin--rollbacktranset@msg=error3returnend

select@weekday=datepart(weekday,dateadd(day,@i,{fncurdate()}))set@i=@i+1if(@weekday=1)breakend

set@j=1fetchnextfromcur_watchingperson4into@PersonID--假如超越界限转头行列第一名if@@fetch_status=-1fetchfirstfromcur_watchingperson4into@PersonIDend

Closecur_watchingPerson4deallocatecur_watchingPerson4

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

--男人天天1人值日班绝对简单declarecur_watchingPerson2scrollcursorForselectPersonidfromwatching_personwherepart=2orderbyorderCodeasc

opencur_watchingPerson2

--挪动到入手下手地位fetchabsolute@malefromcur_watchingPerson2into@PersonIDif@@fetch_status=-1fetchfirstfromcur_watchingperson2into@PersonID

set@i=1

while@i<=30beginupdatewatchingsetMaleId=@PersonIDwhere[datetime]=(dateadd(day,@i,{fncurdate()}))if(@@error!=0)Beginrollbacktranset@msg=error3returnendset@i=@i+1

fetchnextfromcur_watchingperson2into@PersonID--假如超越界限转头行列第一名if@@fetch_status=-1fetchfirstfromcur_watchingperson2into@PersonIDend

Closecur_watchingPerson2deallocatecur_watchingPerson2

--年夜妈每周六周日2人值夜班declarecur_watchingPerson3scrollcursorForselectPersonidfromwatching_personwherepart=3orderbyorderCodeasc

opencur_watchingPerson3

fetchabsolute@femalefromcur_watchingPerson3into@PersonIDif@@fetch_status=-1fetchfirstfromcur_watchingperson3into@PersonID

set@i=1

while@i<=30begin

select@weekday=[weekday]fromwatchingwhere[datetime]=(dateadd(day,@i,{fncurdate()}))--判别只要周末的半天赋值班布置2人if@weekday=7or@weekday=1begin--拔出第一名updatewatchingsetFemale1=@PersonIDwhere[datetime]=(dateadd(day,@i,{fncurdate()}))if(@@error!=0)Beginrollbacktranset@msg=error3returnend

fetchnextfromcur_watchingperson3into@PersonID--假如超越界限转头行列第一名if@@fetch_status=-1fetchfirstfromcur_watchingperson3into@PersonID--拔出第二位updatewatchingsetFemale2=@PersonIDwhere[datetime]=(dateadd(day,@i,{fncurdate()}))if(@@error!=0)Begin--rollbacktranset@msg=error3returnend

endset@i=@i+1fetchnextfromcur_watchingperson3into@PersonID--假如超越界限转头行列第一名if@@fetch_status=-1fetchfirstfromcur_watchingperson3into@PersonIDend

Closecur_watchingPerson3deallocatecur_watchingPerson3

committran



以上为行列改动时天生新值班布置的存储历程

其他诸如划定假期调剂职员迥然不同接待品评斧正








因此我们看到，这些信息足够让我们对单个操作实现“逆操作”。

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油