资源描述：
《数据库专业技术实验六》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、课程名称数据库技术实验成绩实验名称存储过程和触发器的使用学号姓名班级日期14.11.25实验目的：1.掌握存储过程的使用方法；2.掌握触发器的实现方法；实验平台：利用RDBMS（SQLServer2008）及其交互查询工具（查询分析器）来操作T-SQL语言；实验内容：1.存储过程（1）创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。alterprocedureyuangong_infol@EmployeeIDchar(6),@namenchar(20)asbegindeclare@yearints
2、et@year=(selectWorkYearfromEmployeeswhereEmployeeID=@EmployeeID)declare@DepartmentIDchar(3)set@DepartmentID=(selectDepartmentIDfromDepartmentswhereDepartmentName=@name)if(@year>6)updateEmployeessetDepartmentID=@DepartmentIDwhereEmployeeID=@EmployeeIDEndexecdbo.yu
3、angong_infol'000000','经理办公室'（1）创建存储过程，根据每个员工的学历将收入提高500元。alterprocSA_IN@enuchar(6)asbeginupdateSalarysetInCome=InCome+500fromSalary,EmployeeswhereEmployees.EmployeeID=Salary.EmployeeIDandEducation=@enuendselectInComefromSalary,EmployeeswhereSalary.EmployeeID=Empl
4、oyees.EmployeeIDandEducation='本科'goexecdbo.sa_in'本科'goselectInComefromSalary,EmployeeswhereSalary.EmployeeID=Employees.EmployeeIDandEducation='本科'selectInComefromSalary,EmployeeswhereSalary.EmployeeID=Employees.EmployeeIDandEducation='本科'（2）创建存储过程，使用游标计算本科及以上学历的员
5、工在总员工数中的比例。declare@eduvarchar(10),@part_countint,@all_countint;declaremycursorcursorforselectdistincteducation,COUNT(education)over(partitionbyeducation)aspart_count,COUNT(education)over()asall_countfromEmployeesopenmycursorfetchnextfrommycursorinto@edu,@part_cou
6、nt,@all_countwhile@@FETCH_STATUS=0beginprint@edu+'占总人数比例：'+convert(varchar(100),convert(numeric(38,2),@part_count/1.0/@all_count*100))+'%'fetchnextfrommycursorinto@edu,@part_count,@all_countendclosemycusordeallocatemycursor（1）使用命令方式修改及删除一个存储过程。ifexists(selectwork
7、yearfromEmployeeswhereworkyear=3)dropprocedureworkyear2.触发器（1）对于YGGL数据库，表Employees的Employeeid列与表Salary的Employeeid列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。createtriggerSalaryins0onSalaryforinsert,updateasbeginif(selectemployeeidfrominserted)notin(selectEmployeeIDfromEmployee
8、s)rollbackendcreatetriggerEmployeesupdate0ondbo.EmployeesforupdateasbeginupdateSalarysetemployeeid=(selectemployeeidfrominserted)whereemployeeid=(selectemploye
