数据库技术实验六.doc

课程名称数据库技术实验成绩实验名称存储过程和触发器的使用学号姓名班级日期14.11.25实验目的：1. 掌握存储过程的使用方法；2. 掌握触发器的实现方法；实验平台：利用RDBMS（SQL Server 2008）及其交互查询工具（查询分析器）来操作T-SQL语言；实验内容：1. 存储过程（1） 创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。alter procedure yuangong_infol EmployeeID char(6),name nchar(20)as begin declare year int 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=EmployeeIDEndexec dbo.yuangong_infol 000000,经理办公室（2） 创建存储过程，根据每个员工的学历将收入提高500元。alter proc SA_IN enu char(6)asbeginupdate Salaryset InCome=InCome+500from Salary,Employeeswhere Employees.EmployeeID=Salary.EmployeeID and Education=enuendselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科goexec dbo.sa_in 本科goselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科select InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科（3） 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中的比例。declare edu varchar(10), part_count int, all_count int ;declare mycursor cursor for select distinct education, COUNT(education) over(partition by education) as part_count, COUNT(education) over() as all_count from Employees open mycursorfetch next from mycursor into edu,part_count,all_count while FETCH_STATUS=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_countendclose mycusordeallocate mycursor（4） 使用命令方式修改及删除一个存储过程。if exists(select workyear from Employees where workyear=3) drop procedure workyear2. 触发器（1） 对于YGGL数据库，表Employees的Employeeid列与表Salary的Employeeid列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。create trigger Salaryins0 on Salaryfor insert,update asbeginif(select employeeid from inserted) not in(select EmployeeID from Employees)rollbackendcreate trigger Employeesupdate0 on dbo.Employeesfor updateasbeginupdate Salaryset employeeid=(select employeeid from inserted)where employeeid=(select employeeid from deleted)endcreate trigger Employeesdelete0 on Employeesfor deleteasbegindelete from Salarywhere employeeid=(select employeeid from deleted)end（2）当修改表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 income=income + (a-b)*500where enployeeid in(select EmployeesID from inserted)endupdate Employeesset workyear=12where EmployeesID=000001（3）创建UPDATE触发器，当Salary表中InCome值增加500时，outCome值则增加50。create trigger sa_income on Salaryfor update as beginif(select income from inserted)-(select income from deleted)=500)update Salary set outcome=outcome+50where enployeeid=(select enployeeid from inserted)end select income,outcomefrom Salary where enployeeid=000001（5） 创建INSTEAD OF触发器，实现向不可更新视图插入数据。create view a_viewasselect Employees.EmployeesID,name,workyear,income,outcomefrom Employees,Salarywhere Employees.EmployeesID=Salary.enployeeidgocreate trigger gxston a_viewinstead of insertasbegindeclare Ei char(6),name char(10),wy tinyint,ic float,oc floatselect Ei=EmployeesID,name=name,wy=workyear,ic=income,oc=outcomefrom insertedinsert into 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=000011（5）创建DDL触发器，当删除数据库时，提示“无法删除”并回滚删除操作。create t