数据库技术实验六

实验目的：

1. 掌握存储过程的使用方法;

2. 掌握触发器的实现方法；

rjM_K.IGG...o.hpl

叮碍 SUUieryJ.E . itir

血

Ecus ton

ardurji 5ex

V.'crtYMr ⑷崔

加础ur 怙

Wil

True

中憎

0帧

Tnz 3

SJ21121 1

(2)创建存储过程，根据每个员工的学历将收入提高500元

alter proc SA_IN @enuchar(6)

as

begin

update Salary

set InCome=InCome+500 from Salary,Employees

where Employees.EmployeeID =Salary.EmployeeID and Education=@enu end

select In Come

from Salary,Employees

where Salary.EmployeeID = Employees.EmployeeID and Education ='本科' go

execdbo.sa_ in'本科'

go

select In Come

from Salary,Employees

where Salary.EmployeeID = Employees.EmployeeID and Educati0门=本科' select In Come

from Salary,Employees where Salary.EmployeeID =Employees.EmployeeID

■I■ ■ ■ H

1 20B

2 ；

4 52A&7D1 34&D7 3031.55

inCcwii*

1；2532

2

3293701

4羽曲才

号3521

（3）创建存储过程，使用游标计算本科及以上学历的员工在总员工数中的比例。declare

@edu varchar ( 10),

@part_cou nt int 5

@all_cou nt int J

declare mycursor cursor for

select disti net educati on ,

COUNT( education ) over ( partition

by education ) as part_cou nt ,

COUNT( education ) over () as all count

and Educati0门=本科

from Employees ope n mycursor fetch n ext from

mycursor into @edu, @part_count , @all_count while @@FETCH_STATUS= 0 begin print

@edu +'占总人数比例：

'+co nvert ( varchar (100), con vert (n umeric (38,2), @part_cou nt

/ 1.0 / @all_cou nt

*100))+ '%'

fetch n ext from mycursor into @edu, @part_count , @all_count

end

close mycusor deallocate mycursor

(4) 使用命令方式修改及删除一个存储过程。

if exists( select workyear from Employees where workyear =3)

drop procedure workyear

2. 触发器

(1) 对于 YGGL 数据库，表 Employees 的Employeeid 列与表 Salary 的

Employeeid 列应满足参照完整性规则，请用触发器实现两个表间的参照完整 性。

create trigger SalaryinsO on Salary for insert,update as begin

if (select employeeid from in serted) not in (select EmployeelD from Employees) rollback end

create trigger Employeesupdate0 on dbo.Employees for update as begin

update Salary

set employeeid =(select employeeid from inserted) where employeeid =(select employeeid from deleted) end

create trigger Employeesdelete0 on Employees for delete as

亍

o

7

第

B

侶代另

begin

delete from Salary

where employeeid =(select employeeid from deleted)

end

(2)当修改表

Employees时，若将Employees表中员工的工作时间增加 1 年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减

少则无变化。

create trigger em_workyear on Employees

after update

as

begin

declare @a int ,@b int

set @a=( select workyear from in serted )

set @b=( select workyear from deleted )

if (@a>@b)

update Salary

set in come =in come + ( @a- @b>* 500

where enployeeid in( select EmployeesID from inserted )

end

update Employees

set workyear =12

where EmployeesID ='000001'

(3)创建UPDATE触发器，当Salary 表中In Come值增加500时，outCome 值则增加50。

create trigger sa_in come on Salary

for update as begi n

if (( select in come from in serted )-( select in come from deleted )= 500 )

update Salary set outcome =outcome + 50

where en ployeeid = (select en ployeeid from in serted )

end select in come , outcome

from Salary where en ployeeid ='000001'

二结果J消息

incorne outcorne

12100.8123.M

5) 创建INSTEAD OF触发器，实现向不可更新视图插入数据create view a_view

as

select Employees . EmployeesID , name , workyear , income , outcome from Employees , Salary