SQL Server2000视图、存储过程、触发器学习笔记

最近写了几个存储过程、视图和触发器，因为是第一次写，所以没有考虑执行效率，也没有进行优化。不过通过实践发现SQLServer里面的帮助真的很详细很有用！

一、对原始的考勤数据进行统计，SQL存储过程如下：

CREATE Proc Proc_DayCollect
@SigDateT datetime
as
declare @CardNum varchar(20),@Emplyee_Name nchar(10)
declare @Status1 nchar(1),@Status2 nchar(1),@Status3 nchar(1)
declare @SigDate varchar(10)
Set @SigDate=convert(varchar(10),@SigDateT,120)
Set @Status1 = '√'
Set @Status2 = '○'
Set @Status3 = '╳'
declare @AM_OnT varchar(5),@AM_OffT varchar(5),@PM_OnT varchar(5),@PM_OffT varchar(5),@Night_OnT varchar(5),@Night_OffT varchar(5)
declare @AM_OnR nchar(1),@AM_OffR nchar(1),@PM_OnR nchar(1),@PM_OffR nchar(1),@Night_OnR nchar(1),@Night_OffR nchar(1)
declare @Holiday int,@Evection int,@Leave int
set @Holiday = 0
--判断今天是否公休日
Exec Proc_GetHoliday @SigDate,@Holiday output
--统计签到、签退时间 #TempWorkRest
Declare @Start_On datetime, @End_On datetime, @Start_Off datetime, @End_Off datetime
Declare @SectionName varchar(10),@SectionEnabled bit
select SectionName, SectionEnabled, DateAdd(mi,-OnValid,@SigDate+' '+On_Time+':00') as Start_On,
    DateAdd(mi,OnLate,@SigDate+' '+On_Time+':00') as End_On,
    DateAdd(mi,-OffEarly,@SigDate+' '+Off_Time+':00') as Start_Off,
    DateAdd(mi,OffValid,@SigDate+' '+Off_Time+':00') as End_Off
into #TempWorkRest
from Ex_WorkRest
    
--统计员工刷卡信息
declare EmplyeeCrsr cursor for select CardNum,Emplyee_Name from Ex_Emplyee
Open EmplyeeCrsr
fetch EmplyeeCrsr into @CardNum,@Emplyee_Name
while @@fetch_status = 0
begin
 if @Holiday = 0
 begin
 --查询是否出差或请假
 set @Evection = 0
 set @Leave = 0
 Exec Proc_GetLeave @CardNum,@SigDate,@Evection output,@Leave output
 if @Evection=0 and @Leave=0
 begin
 --查询某员工该天刷卡记录
-- declare WorkRestCrsr cursor for select SectionName,SectionEnabled,Start_On,End_On,Start_Off,End_Off from #TempWorkRest
 declare WorkRestCrsr cursor for select * from #TempWorkRest
 Open WorkRestCrsr
 fetch WorkRestCrsr into @SectionName,@SectionEnabled,@Start_On,@End_On,@Start_Off,@End_Off
 while @@fetch_Status = 0
 begin
  --根据作息时间表判断
  if @SectionName='上午'              --上午的考勤情况
  begin
   if @SectionEnabled=1
   begin
    set @AM_OnT=null
    set @AM_OffT=null
    select @AM_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_On and SigTime <= @End_On
    select @AM_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_Off and SigTime <= @End_Off
    if @AM_OnT is null
    begin
     select Top 1 @AM_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime
     if @AM_OnT is not null
     begin
      set @AM_OnR=@Status2
     end
     else
     begin
      set @AM_OnT=''
      set @AM_OnR=@Status3
     end
    end 
    else
     set @AM_OnR = @Status1
    if @AM_OffT is null
    begin
     select Top 1 @AM_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime Desc
     if @AM_OffT is not null and @AM_OnT<>@AM_OffT
     begin
      set @AM_OffR=@Status2
     end
     else
     begin
      set @AM_OffT=''
      set @AM_OffR=@Status3
     end
    end 
    else
    begin
     set @AM_OffR = @Status1
    end
   end
   else
   begin
    set @AM_OnT = ''
    set @AM_OffT = ''
    set @AM_OnR = '' 
    set @AM_OffR = ''  
   end
  end
  if @SectionName='下午'   --下午的考勤情况
  begin
   if @SectionEnabled=1
   begin
    set @PM_OnT=null
    set @PM_OffT=null
    select @PM_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_On and SigTime <= @End_On
    select @PM_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_Off and SigTime <= @End_Off
    if @PM_OnT is null
    begin
     select Top 1 @PM_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime
     if @PM_OnT is not null
     begin
      set @PM_OnR=@Status2
     end
     else
     begin
      set @PM_OnT=''
      set @PM_OnR=@Status3
     end
    end 
    else
    begin
     set @PM_OnR = @Status1
    end
    if @PM_OffT is null
    begin
     select Top 1 @PM_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime Desc
     if @PM_OffT is not null and @PM_OnT<>@PM_OffT
     begin
      set @PM_OffR=@Status2
     end
     else
     begin
      set @PM_OffT=''
      set @PM_OffR=@Status3
     end
    end 
    else
    begin
     set @PM_OffR = @Status1   
    end
   end
   else
   begin
    set @PM_OnT = ''
    set @PM_OffT = '' 
    set @PM_OnR = ''
    set @PM_OffR = ''    
   end
  end
  if @SectionName='晚间'   --晚间的考勤情况
  begin
   if @SectionEnabled=1
   begin
    set @Night_OnT=null
    set @Night_OffT=null
    select @Night_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_On and SigTime <= @End_On
    select @Night_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
    where CardNum=@CardNum and SigTime >= @Start_Off and SigTime <= @End_Off
    if @Night_OnT is null
    begin
     select Top 1 @Night_OnT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime
     if @Night_OnT is not null
     begin
      set @Night_OnR=@Status2
     end
     else
     begin
      set @Night_OnT=''
      set @Night_OnR=@Status3
     end
    end 
    else
    begin
     set @Night_OnR = @Status1
    end
    if @Night_OffT is null
    begin
     select Top 1 @Night_OffT=Convert(varchar(5),SigTime,108) from Ex_SigRecord
     where CardNum=@CardNum and SigTime > @End_On and SigTime < @Start_Off
     Order By SigTime Desc
     if @Night_OffT is not null and  @Night_OnT<>@Night_OffT
     begin
      set @Night_OffR=@Status2
     end
     else
     begin
      set @Night_OffT=''
      set @Night_OffR=@Status3
     end
    end 
    else
    begin
     set @Night_OffR = @Status1   
    end
   end
   else
   begin
    set @Night_OnT = ''
    set @Night_OffT = '' 
    set @Night_OnR =''
    set @Night_OffR = ''   
   end
  end  
  fetch next from WorkRestCrsr into @SectionName,@SectionEnabled,@Start_On,@End_On,@Start_Off,@End_Off
 end
 Close WorkRestCrsr
 Deallocate WorkRestCrsr
 end
 else
 begin                --公差或请假
  set @AM_OnT = ''
  set @AM_OffT = ''
  set @PM_OnT = ''
  set @PM_OffT = ''
  set @Night_OnT = ''
  set @Night_OffT = ''
  set @AM_OnR = ''
  set @AM_OffR = ''
  set @PM_OnR = ''
  set @PM_OffR = ''
  set @Night_OnR = ''
  set @Night_OffR = ''
 end
 end
 else
 begin                   --公休的情况
  set @Evection = 0
  set @Leave = 0
  set @AM_OnT = ''
  set @AM_OffT = ''
  set @PM_OnT = ''
  set @PM_OffT = ''
  set @Night_OnT = ''
  set @Night_OffT = ''
  set @AM_OnR = ''
  set @AM_OffR = ''
  set @PM_OnR = ''
  set @PM_OffR = ''
  set @Night_OnR = ''
  set @Night_OffR = ''
 end
 --插入记录
 INSERT INTO Ex_DayCollect
  ( [SigDate],[CardNum],[Emplyee_Name],[HoliDay],[Evection],[Leave],[AM_OnT],[AM_OnR],[AM_OffT],[AM_OffR],
 [PM_OnT],[PM_OnR],[PM_OffT],[PM_OffR],[Night_OnT],[Night_OnR],[Night_OffT],[Night_OffR])
  VALUES
 ( @SigDate,@CardNum,@Emplyee_Name,@HoliDay,@Evection,@Leave,@AM_OnT,@AM_OnR,@AM_OffT,
  @AM_OffR,@PM_OnT,@PM_OnR,@PM_OffT,@PM_OffR,@Night_OnT,@Night_OnR,@Night_OffT,@Night_OffR)
 fetch Next from EmplyeeCrsr into @CardNum, @Emplyee_Name
end
Close EmplyeeCrsr
Deallocate EmplyeeCrsr
Drop Table #TempWorkRest
GO

二、为了对日考勤数据进行月统计，SQL视图如下

CREATE VIEW dbo.VIEW_Month
AS
SELECT CONVERT(varchar(7), SigDate, 120) AS 月份, CardNum AS 卡号,
      Emplyee_Name AS 姓名, SUM(HoliDay) AS [公休(天)], SUM(Evection) AS [出差(天)],
      SUM(Leave) AS [请假(天)], SUM(CASE WHEN Am_OnR = '╳' AND
      Am_OffR = '╳' THEN 1 ELSE 0 END) + SUM(CASE WHEN Pm_OnR = '╳' AND
      Pm_OffR = '╳' THEN 1 ELSE 0 END) + SUM(CASE WHEN Night_OnR = '╳' AND
      Night_OffR = '╳' THEN 1 ELSE 0 END) AS [缺勤(次)],
      SUM(CASE WHEN Am_OnR = '╳' AND Am_OffR = '√' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Pm_OnR = '╳' AND Pm_OffR = '√' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Night_OnR = '╳' AND Night_OffR = '√' THEN 1 ELSE 0 END)
      AS [未签到(次)], SUM(CASE WHEN Am_OnR <> '╳' AND
      AM_OffR = '╳' THEN 1 ELSE 0 END) + SUM(CASE WHEN Pm_OnR <> '╳' AND
      PM_OffR = '╳' THEN 1 ELSE 0 END) + SUM(CASE WHEN Night_OnR <> '╳' AND
      Night_OffR = '╳' THEN 1 ELSE 0 END) AS [未签退(次)],
      SUM(CASE WHEN Am_OnR = '○' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Pm_OnR = '○' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Night_OnR = '○' THEN 1 ELSE 0 END) AS [迟到(次)],
      SUM(CASE WHEN Am_OffR = '○' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Pm_OffR = '○' THEN 1 ELSE 0 END)
      + SUM(CASE WHEN Night_OffR = '○' THEN 1 ELSE 0 END) AS [早退(次)]
FROM dbo.Ex_DayCollect
GROUP BY CardNum, Emplyee_Name, CONVERT(varchar(7), SigDate, 120)

三、为了方便处理建立了几个触发器，如下

CREATE TRIGGER Trg_Del ON dbo.Ex_WMsg
FOR DELETE
AS
insert into Ex_SMsg select * from deleted

这些基本上是我这段时间的学习成果吧，不过我认为我还有很多不明白的地方。本来还想建一个关系图，后来感觉就十几表没必要那么麻烦，就没弄。