视图.txt

自己做过的一个关于SQL数据库的课程设计

1
alter view EmpCommonInfo员工的详细信息
as
select 编号,姓名,电子邮件,电话,
       登录名,登录密码,基本工资,报到日期,
       部门名称,自我介绍,剩余假期,
      (select 电子邮件 from dbo.tblEmployee员工基本信息 where 职位编号 in ('002','003','004','005')) as 经理电子邮件,
      (select 姓名 from dbo.tblEmployee员工基本信息 where 职位编号 in ('002','003','004','005'))  as 经理姓名


FROM dbo.tblDepartment内部的部门信息 LEFT OUTER JOIN
      dbo.tblEmployee员工基本信息 ON 
      dbo.tblDepartment内部的部门信息.部门编号 = dbo.tblEmployee员工基本信息.部门编号 and
     tblEmployee员工基本信息.职位编号=dbo.tblTitle公司员工各种职位.职位编号


2

create view AllAttend员工考勤
as
select tblAttendance员工的缺勤信息.编号,部门编号,缺勤者姓名,缺勤类型,缺勤小时数,
       缺勤日期
from tblAttendance员工的缺勤信息,tblEmployee员工基本信息
where tblAttendance员工的缺勤信息.编号=tblEmployee员工基本信息.编号

3

alter view viwManagerInfo经理基本信息
as
select 姓名,电子邮件,部门名称,部门经理编号,tblDepartment内部的部门信息.部门编号,dbo.tblEmployee员工基本信息.职位编号,
      (select 电子邮件 from dbo.tblEmployee员工基本信息 where 职位编号 in ('002','003','004','005')) as 经理电子邮件,
      (select 姓名 from dbo.tblEmployee员工基本信息 where 职位编号 in ('002','003','004','005'))  as 经理姓名
from tblDepartment内部的部门信息,tblEmployee员工基本信息,dbo.tblTitle公司员工各种职位
where tblDepartment内部的部门信息.部门编号=tblEmployee员工基本信息.部门编号 and
      tblEmployee员工基本信息.职位编号=dbo.tblTitle公司员工各种职位.职位编号










	存储过程



create procedure 请假申请
@编号 int,@请假者姓名 char(10),@提交时间 datetime,
@开始时间 datetime,@结束时间 datetime,@请假类型 char(10),@申请状态 char(10)
as
select @编号=编号,@请假者姓名=请假者姓名,@提交时间=提交时间,
       @开始时间=开始时间,@结束时间=结束时间,@请假类型=请假类型,
       @申请状态=申请状态
from dbo.tblLeave员工的请假记录




exec 请假申请 @编号=0006,@请假者姓名='胡萝卜',@提交时间='2007-02-02',@开始时间='2007-02-03',
@结束时间='2007-02-10',@请假类型='病假',@申请状态='已提交'


第二题
此存储过程通过更新表 tblAttendance 的 AskReview（请求重新审核） 
字段值为1，并更新tblAttendance 的AskReviewReason（请求重新审核原因）来提交要求复查一条考勤记录的信息


alter proc 重新审核
@a varchar(3),@b varchar(50)
as

update tblAttendance员工的缺勤信息 set 请求重新审核='1' where 编号=@a
update tblAttendance员工的缺勤信息 set 请求重新审核原因=@b where 编号=@a

exec 重新审核 @a='1',@b='错误'




此存储过程根据tblAttendance（考勤）表信息按月更新tblSalary（薪资）
表的AbsenceSalary（缺勤工资）列

CREATE proc 按月更新
@时间 int,@d int
as

declare 更新 cursor
for

select dbo.tblAttendance员工的缺勤信息.编号,基本工资,sum(缺勤小时数) as 每个员工总缺勤小时数
from dbo.tblAttendance员工的缺勤信息,dbo.tblSalary员工每月的工资信息
where dbo.tblSalary员工每月的工资信息.编号=dbo.tblAttendance员工的缺勤信息.编号  
group by  dbo.tblAttendance员工的缺勤信息.编号,基本工资 

open 更新
declare @编号 varchar(3),@基本工资 float,@每个员工总缺勤小时数 float,@缺勤扣除 float
fetch next from cur into @编号,@基本工资,@每个员工总缺勤小时数
set @时间=@d
if @时间=28
begin

  while @@fetch_status=0
  begin
    set  @缺勤扣除=(select 缺勤小时数*10 from dbo.tblAttendance员工的缺勤信息 )
                              
    update dbo.tblSalary员工每月的工资信息 set 基本工资=(基本工资+加班工资-@缺勤扣除)

    fetch next from cur into @编号,@基本工资,@每个员工总缺勤小时数
    print '编号'+@编号+'基本工资'+@基本工资+'每个员工总缺勤小时数'+@每个员工总缺勤小时数
  end
end
  close 更新
  deallocate 更新



GO










触发器
此触发器在部门经理接受员工加班申请时触发。当员工请求加班类型为
“折算成津贴”，按员工加班小时数、每小时津贴计算员工加班工资更新tblSalary（
工资）表的OvertimeSalary（加班工资）字段。当员工请求加班类型为“折算成年假”
，计算员工加班小时数更新tblEmployee（员工信息）
表的VacationRemain（剩余假期小时数）字段。

             第一题
CREATE trigger 加班申请时触发1
on dbo.tblOvertime员工的加班记录
for update
as
if update(申请状态)
declare @a varchar(10),@b varchar(10)
begin
set @a=(select 申请状态 from inserted where 申请状态='已批准')
  if(@a='已批准')
   begin
set @b=(select 加班类型 from inserted )

   if (@b='折算成津贴')
   begin
      update tblSalary员工每月的工资信息
      set 加班工资=(select 100 * tblOvertime员工的加班记录.加班小时数+加班工资 
                  from tblSalary员工每月的工资信息,
                tblOvertime员工的加班记录,inserted	
       where tblSalary员工每月的工资信息.编号= inserted.编号)
    
       update tblSalary员工每月的工资信息 
       set 加班工资=(select 加班工资-tblOvertime员工的加班记录.加班小时数*100  
                  from tblSalary员工每月的工资信息,
               tblOvertime员工的加班记录,deleted
       where tblSalary员工每月的工资信息.编号=deleted.编号)
   end
   else if(@b='折算成年假')
     begin
      update tblEmployee员工基本信息
      set 剩余假期=(select 加班小时数+剩余假期 
      from inserted,dtblOvertime员工的加班记录
      where inserted.编号=tblEmployee员工基本信息.编号)

      update tblEmployee员工基本信息
      set 剩余假期=(select 剩余假期-加班小时数
      from deleted,dtblOvertime员工的加班记录
      where deleted.编号=tblEmployee员工基本信息.编号)
      end      
   end
end






第二题

alter trigger 请假申请时触发
on dbo.tblLeave员工的请假记录
for update,insert
as
if update(申请状态)
declare @a varchar(10),@b varchar(10)
begin
set @a=(select 申请状态 from inserted where 申请状态='已批准')
  if(@a='已批准')
   begin
set @b=(select 请假类型 from inserted )

   if (@b='折算成津贴')
   begin
       update dbo.tblSalary员工每月的工资信息
       set 缺勤扣除=(select tblLeave员工的请假记录.请假小时数*10+dbo.tblSalary员工每月的工资信息.缺勤扣除 
       from tblLeave员工的请假记录,tblSalary员工每月的工资信息,inserted  
       where tblSalary员工每月的工资信息.编号=inserted.编号)
    
       update dbo.tblSalary员工每月的工资信息
       set 缺勤扣除=(select tblSalary员工每月的工资信息.缺勤扣除-tblLeave员工的请假记录.请假小时数*10 
       from tblLeave员工的请假记录,tblSalary员工每月的工资信息,deleted
       where tblSalary员工每月的工资信息.编号=deleted.编号 )
   end
   else if(@b='折算成年假')
     begin
      update tblEmployee员工基本信息
      set 剩余假期=(select 剩余假期-dbo.tblLeave员工的请假记录.请假小时数
      from inserted,dbo.tblLeave员工的请假记录
      where inserted.编号=tblEmployee员工基本信息.编号)

      update tblEmployee员工基本信息
      set 剩余假期=(select 剩余假期+dbo.tblLeave员工的请假记录.请假小时数
      from deleted,dbo.tblLeave员工的请假记录
      where deleted.编号=tblEmployee员工基本信息.编号)
      end      
   end
end


          第三题

alter trigger 请求请假类型
on dbo.tblLeave员工的请假记录
for insert
as
declare @b varchar(10),@a varchar(10),@c varchar(10)
begin
set @b=(select 请假类型 from inserted )
   if (@b='折算成年假')
   begin
     set @a=(select 剩余假期 from dbo.tblEmployee员工基本信息)
     set @c=(select 请假小时数 from dbo.tblLeave员工的请假记录)
     if(@a<@c)
        print '您的剩余假期以经用完了，不能请假了'  
   end
end















--用游标输出基本工资等于2000的员工信息，逐个输出。
create proc 员工信息
declare @a varchar
as
declare cur cursor
for
select 编号,姓名,登入名,电子邮件,部门编号,sum(基本工资) as 每个人的基本工资
from tblEmployee员工基本信息
where 基本工资='2000'
group by 编号,姓名,登入名,电子邮件,部门编号
open cur
fetch next from cur
while @@fetch_status=0
begin
  if(基本工资='2000')
  begin
   select 编号,姓名,登入名,电子邮件,部门编号
   from tblEmployee员工基本信息
  fetch next