SQL 2008 课后习题答案 实验7

1.存储过程
1>创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程

create procedure test @number1 int output
as
begin
declare @number2 int
set @number2=(select COUNT (*)from Employees)
set @number1=@number2
end

执行该存储过程，并查看结果；

declare @num int
exec test @num output
select @num

2>创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1；

create procedure COMPA @ID1 char(6),@ID2 char(6),@BJ int output
as
begin
declare @SR1 float,@SR2 float
select @SR1=InCome-OutCome from Salary where EmployeeID=@ID1
select @SR2=InCome-OutCome from Salary where EmployeeID=@ID2
if @ID1>@ID2
set @BJ=0
else
set @BJ=1
end

执行该存储过程，并查看结果；

declare @BJ int
exec COMPA '000001','108991',@BJ output
select @BJ

3>创建添加职员记录的存储过程EmploeeAdd；

create procedure EmployeeAdd
(
@EmployeeID char(6),@Name char(10),@Education char(4),@Birthday datetime,
@WorkYear tinyint,@Sex bit,@Address char(40),@PhoneNumber char(12),
@DepartmentID char(3)
)as
begin
insert into Employees
values(@EmployeeID,@Name,@Education,@Birthday,@WorkYear,@Sex,@Address,@PhoneNumber,@DepartmentID)
end
return
go

执行该存储过程；

exec EmployeeAdd '990230','刘朝','本科','840909',2,1,'洪武小洪山5号','85465213','3'

4>创建一个带有output游标参数的存储过程，在Employees表中声明并打开一个游标；

create procedure em_cursor @em_cursor cursor varying output
as
begin
set @em_cursor=CURSOR forward_only static
FOR
select * from Employees
open @em_cursor
end

声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录；

declare @MyCursor cursor
exec em_cursor @em_cursor=@MyCursor output
fetch next from @MyCursor
while (@@FETCH_STATUS=0)
begin
fetch next from @MyCursor
end
close @MyCursor
deallocate @MyCursor
go

5>创建存储过程，使用游标确定一个员工的实际收入是否排在前三名。结果为1表示是，结果为0表示否；


create procedure top_three @em_id char,@ok bit output
as
begin
declare @x_em_id char(6)
declare @act_in int,@seq int
declare salary_dis cursor for
select EmployeeID,InCome-OutCome from Salary
order by InCome-OutCome desc
set @seq=0
set @ok=0
open salary_dis
fetch salary_dis into @x_em_id,@act_in
while @seq<3 and @ok=0
begin
set @seq=@seq+1
if @x_em_id=@em_id
set @ok=1
fetch salary_dis into @x_em_id,@act_in
end
close salary_dis
deallocate salary_dis
end

执行该存储过程，并查看结果；

declare @ok bit
exec top_three '108991',@ok output
select @ok

6>创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工

作；


7>创建存储过程，根据每个员工的学历将收入提高500元；

8>创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例；

9>使用命令方式修改存储过程的定义；

2.触发器

1>向Employees表插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作；

create trigger EmployeesIns on dbo.Employees
for insert,update
as
begin
if ((select DepartmentID from inserted)not in
(select DepartmentID from Departments))
rollback
end

向Employees表插入或修改一行记录，查看效果；

2>修改Departments 表“DepartmentID”字段时，该字段在Employees表中的对应值也做相应修改；


create trigger DepartmnetsUpdate on dbo.Departments
for update
as
begin
update Employees
set DepartmentID=(select DepartmentID from inserted)
where DepartmentID=(select DepartmentID from deleted)
end
go


3>删除Departments表中记录的同时删除该记录“DepartmentID”字段值在Employees表中对应的记录；

create trigger DepartmnetsUpdate on dbo.Departments
for update
as
begin
update Employees
set DepartmentID=(select DepartmentID from inserted)
where DepartmentID=(select DepartmentID from deleted)
end
go

4>创建instead of 触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，
若存在则执行插入操作，若不存在则提示“员工号不存在”

create trigger em_exists on Salary
instead of insert
as
begin
declare @EmployeeID char(6)
select @EmployeeID=EmployeeID
from inserted
if(@EmployeeID in(select EmployeeID from Employees))
insert into Salary select*from inserted
else
print '员工号不存在'
end

向Salary表中插入一行记录查看效果；

insert into Salary values('111111',2500.3,123.2)

5>创建DDL触发器，当删除YGGL数据库的一个表时，提示“不能删除该表”，并回滚删除表的操作；

create trigger table_delete
on database
after drop_table
as
print '不能删除该表'
rollback transaction

6>对于YGGL数据库，表Employees的EmployeeID列与表Salary的EmployeeID列应满足参照完整性规则，
请用触发器实现两个表间的参照完整性；

7>当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年
则增加1000，依次增加。若工作时间减少则无变化；

8>创建update触发器，当Salary表中Income值增加500时，Outcome值则增加50；

9>创建instead of触发器，实现向不可更新视图插入数据；

10>创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操作；