数据库技术实验六

1、 课程名称 数据库技术 实验 成绩 实验名称 存储过程和触发器的使用 学号 姓名 班级 日期 14.11.25 实验目的： 1. 掌握存储过程的使用方法; 2. 掌握触发器的实现方法； 实验平台： 利用RDBMS( SQL Server 2008)及其交互查询工具(查询分析器)来操作T-SQL 语言； 语言； 实验内容： 1.存储过程 (1) 创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理 办公室工作。 alter procedure yuangong_infol EmployeelD char(6),name nchar(20) as begin declare year in

2、t set year=( select WorkYear from Employees where EmployeeID =EmployeeID) declare DepartmentID char(3) set DepartmentID =( select DepartmentID from Departments where DepartmentName =name) if (year6) update Employees set DepartmentID =DepartmentID where EmployeeID =EmployeeID End execdbo.yuangong_inf

3、ol 000000,经理办公室 Tvgrm Wwvl ! e.tr * E5B 鹘 轴r23 r.e JlRNd 4U4 r :M 1 口砂 R.f I L 4= - - 亠.u -=一 (2) 创建存储过程，根据每个员工的学历将收入提高500元 alter proc SA_INenu char(6) as begin update Salary set InCome=InCome+500 from Salary,Employees where Employees.EmployeeID=Salary.EmployeeID a nd Educati on=enu end select In Co

4、me from Salary,Employees where Salary.EmployeelD=Employees.EmployeelD a nd Educatio n=本科 go execdbo.sa_ in本科 go select In Come from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID a nd Educatio n=本科 select In Come from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID a nd

5、Educatio n=本科 1 :20奁 C2 富 3 1 4 MW 7 31 90 taCtNW 2502 C 2 ； 2 S3 2JS7.D1 3S31 M (3) 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中的 比例。 declare edu varchar (10), part_count int, all_count int ; declare mycursor cursor for select distineteducation , COUNT(education ) over (partition by education ) as part_count , CO

6、UNT(education ) over() asall count from Employees open mycursor fetch next from mycursor into edu,part_count ,all_count while FETCH_STATU=0begin print edu+占总人数比例： +convert (varchar(100),convert (numeric (38,2),part_count /1.0/all_count *100)+% fetch next from mycursor into edu ,part_count ,all_count

7、 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 Salaryins0 on Salary for insert,update as b

8、egin if(select employeeid from in serted) not in (select EmployeeID 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

9、 Employeesdelete0 on Employees for delete as 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

10、 a=(select workyear from inserted ) set b=(select workyear from deleted ) if(ab) update Salary set in come = in come + (a-b)*500 where en ployeeid in (select EmployeesID from in serted ) end update Employees set workyear =12 where EmployeesID =000001 (3) 创建 UPDATE触发器，当 Salary表中In Come值增加500时，outCome

11、 值则增加50。 create trigger sa_income on Salary for update as begin if(select in come from in serted )-(select in come from deleted )=500) update Salary set outcome =outcome +50 where enployeeid =(select enployeeid from inserted ) end select in come ,outcome from Salary where enployeeid =000001 q 1 L3结廉

12、 消息 ifieame outecirift !l!a!lll!*lll!R*!IIHL 1 i 21M.& = 123.09 Mill IIK （5）创建INSTEAD OF触发器，实现向不可更新视图插入数据 create view a view as select Employees.EmployeesID,name ,workyear ,income outcome from Employees ,Salary where Employees .EmployeesID =Salary.enployeeid go create trigger gxst on a_view in stead

13、 of in sert as begin declare Eichar(6),name char(10),wy tinyint ,ic float ,oc float select Ei=EmployeesID, n ame = name ,wy=workyear ,ic= in come ,oc=outcome from inserted insert into Employees(EmployeesID,name,workyear) values(Ei,name ,wy) insert into Salary values(Ei,ic,oc) end insert into a_view values(000011，小芳,3,2000 ,1500) select * from a_view where EmployeesID =000011 Employe ID InCcm OtriCome1 II Miliilllilnniliiti!i 叶 llh n omn namimm “miimhiil 小旁3 3000 1500 (5)创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操 作。 create trigger table_dele