数据库技术实验六

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)asbegindeclare year intset year=(select WorkYear

2、from Employeeswhere EmployeeID =EmployeeID)declare DepartmentID char(3)set DepartmentID =(select DepartmentIDfrom Departmentswhere DepartmentName =name)if (year6)update Employeesset DepartmentID =DepartmentIDwhere EmployeeID =EmployeeIDEndexecdbo.yuangong_infol 000000,经理办公室 TvgrmWwvl ! e.tr* E5B鹘轴r2

3、3r.eJlRNd4U4r:M1口砂R.fIL4=-亠.u-=一(2) 创建存储过程，根据每个员工的学历将收入提高500元alter proc SA_INenu char(6)asbeginupdate Salaryset InCome=InCome+500from Salary,Employeeswhere Employees.EmployeeID=Salary.EmployeeID a nd Educati on=enuendselect In Comefrom Salary,Employeeswhere Salary.EmployeelD=Employees.EmployeelD a n

4、d Educatio n=本科 goexecdbo.sa_ in本科goselect In Comefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID a nd Educatio n=本科select In Comefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID a nd Educatio n=本科1:20奁 C2 富31 4MW 731 90taCtNW2502 C 2 ；2S32JS7.D13S31 M(3) 创建存储过程，使用

5、游标计算本科及以上学历的员工在总员工数中的 比例。declareedu varchar (10),part_count int,all_count int ;declare mycursor cursor forselect distineteducation ,COUNT(education ) over (partition by education ) as part_count , COUNT(education ) over() asall countfrom Employees open mycursorfetch next frommycursor into edu,part_c

6、ount ,all_countwhile FETCH_STATU=0beginprint edu+占总人数比例：+convert (varchar(100),convert (numeric (38,2),part_count /1.0/all_count *100)+% fetch next from mycursorinto edu ,part_count ,all_countendclose mycusordeallocate mycursor(4)使用命令方式修改及删除一个存储过程。if exists(select workyear from Employees where worky

7、ear =3)drop procedure workyear2.触发器(1) 对于YGGL数据库，表 Employees的Employeeid 列与表 Salary的 Employeeid列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。create trigger Salaryins0 on Salaryfor insert,updateasbeginif(select employeeid from in serted) not in (select EmployeeID from Employees)rollbackend create trigger Employeesupd

8、ate0 on dbo.Employees for update asbeginupdate Salaryset employeeid =(select employeeid from inserted) where employeeid =(select employeeid from deleted ) end create trigger Employeesdelete0 on Employees for deleteasbegindelete from Salarywhere employeeid =(select employeeid from deleted )end(2) 当修改

9、表Employees时，若将 Employees表中员工的工作时间增加 1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化。create trigger em_workyear on Employeesafter updateasbegindeclare a int,b intset a=(select workyear from inserted )set b=(select workyear from deleted )if(ab)update Salaryset in come = in come + (a-b)*500where en ployeeid

10、 in (select EmployeesID from in serted )endupdate Employeesset workyear =12where EmployeesID =000001(3) 创建 UPDATE触发器，当 Salary表中In Come值增加500时，outCome 值则增加50。create trigger sa_income on Salaryfor update as beginif(select in come from in serted )-(select in come from deleted )=500)update Salary set ou

11、tcome =outcome +50where enployeeid =(select enployeeid from inserted )end select in come ,outcomefrom Salary where enployeeid =000001q 1L3结廉消息ifieame outecirift!l!a!lll!*lll!R*!IIHL1 i 21M.& = 123.09Mill IIK（5）创建INSTEAD OF触发器，实现向不可更新视图插入数据create view a viewasselect Employees.EmployeesID,name ,workye

12、ar ,income outcomefrom Employees ,Salarywhere Employees .EmployeesID =Salary.enployeeidgocreate trigger gxston a_viewin stead of in sertasbegindeclare Eichar(6),name char(10),wy tinyint ,ic float ,oc floatselect Ei=EmployeesID, n ame = name ,wy=workyear ,ic= in come ,oc=outcome from insertedinsert i

13、nto Employees(EmployeesID,name,workyear) values(Ei,name ,wy) insert into Salary values(Ei,ic,oc) endinsert into a_viewvalues(000011，小芳,3,2000 ,1500)select * from a_viewwhere EmployeesID =000011Employe IDInCcmOtriCome1II Miliilllilnniliiti!i 叶 llh n omnnamimm “miimhiil小旁330001500(5)创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操 作。create trigger table_delete