1、課程名稱數據庫技術實驗實驗名稱存儲過程和觸發器的使用成績學號姓名班級日期14.11.25實驗目的：1. 掌握存儲過程的使用方法；2. 掌握觸發器的實現方法；實驗平臺：利用RDBMS（ SQL Server 2008）及其交互查詢工具（查詢分析器）來操作T-SQL 語言；實驗內容：1. 存儲過程（ 1） 創建存儲過程，要求當一個員工的工作年份大于6 年時將其轉到經理辦公室工作。alter procedure yuangong_infol EmployeeID char(6),name nchar(20)asbegindeclare year intset year=(select WorkYea

2、rfrom Employeeswhere EmployeeID =EmployeeID )declare DepartmentID char(3)set DepartmentID =(select DepartmentIDfrom Departmentswhere DepartmentName=name)if (year>6)update Employeesset DepartmentID =DepartmentIDwhere EmployeeID =EmployeeIDEndexec dbo.yuangong_infol '000000','經理辦公室 '

3、;（ 2） 創建存儲過程，根據每個員工的學歷將收入提高500 元。alter proc SA_IN enu char( 6)asbeginupdate Salaryset InCome =InCome+500from Salary,Employeeswhere Employees.EmployeeID =Salary.EmployeeID and Education=enu endselect InComefrom Salary,Employeeswhere Salary.EmployeeID =Employees.EmployeeID and Education='本科 '

4、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） 創建存儲過程，使用游標計算本科及以上學歷的員工在總員工數中的比例。declareeduvarch

5、ar( 10),part_countint,all_countint;declaremycursorcursorforselectdistincteducation,COUNT( education)over( partitionby education)aspart_count,COUNT( education)over()as all_countfromEmployeesopenmycursorfetchnextfrommycursorintoedu, part_count, all_countwhileFETCH_STATUS= 0 beginprintedu+ ' 占總人數比例

6、：' + convert( varchar( 100 ), convert( numeric ( 38 , 2 ), part_count/ 1.0 / all_count* 100 )+ '%'fetchnextfrommycursorintoedu, part_count, all_countendclosemycusordeallocatemycursor（ 4） 使用命令方式修改及刪除一個存儲過程。ifexists(selectworkyearfromEmployeeswhereworkyear= 3 )dropprocedureworkyear2. 觸發器（

7、1） 對于 YGGL數據庫，表 Employees 的 Employeeid 列與表 Salary 的Employeeid列應滿足參照完整性規則，請用觸發器實現兩個表間的參照完整性。create trigger Salaryins0 on Salaryfor insert,updateasbeginif (select employeeid from inserted) not in( select EmployeeID from Employees)rollbackendcreate trigger Employeesupdate0 on dbo.Employeesfor updateasb

8、eginupdate 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年，則將收

9、入增加500 ，若增加2 年則增加1000 ，依次增加。若工作時間減少則無變化。createtriggerem_workyearon Employeesafterupdateasbegindeclarea int , b intseta=( selectworkyearfrominserted)setb=( selectworkyearfromdeleted)if ( a>b)updateSalarysetincome= income+( a- b)* 500whereenployeeidin(selectEmployeesIDfrom inserted)endupdateEmploye

10、essetworkyear= 12whereEmployeesID= '000001'（ 3）創建 UPDATE觸發器，當Salary表中 InCome 值增加 500時， outCome值則增加 50 。createtriggersa_incomeonSalaryforupdate as beginif (selectincomefrominserted)-(selectincomefromdeleted)= 500 )updateSalarysetoutcome = outcome+ 50whereenployeeid=( selectenployeeidfrominser

11、ted)end selectincome, outcomefromSalarywhereenployeeid= '000001'（ 5） 創建 INSTEAD OF 觸發器，實現向不可更新視圖插入數據。createviewa_viewasselectEmployees. EmployeesID , name , workyear , income, outcomefromEmployees , SalarywhereEmployees. EmployeesID= Salary. enployeeidgocreatetriggergxston a_viewinsteadof in

12、sertasbegindeclareEichar( 6),name char ( 10 ),wy tinyint, ic float, oc floatselectEi = EmployeesID, name=name , wy=workyear, ic = income, oc= outcomefrominsertedinsertintoEmployees( EmployeesID , name , workyear)values ( Ei , name, wy)insertintoSalaryvalues( Ei , ic , oc)endinsertintoa_viewvalues( '000011','小芳',3,2000 ,1500)select* froma_viewwhereEmployeesID= '000011'（ 5）創建DDL 觸發器，當刪除數據庫時，提示“無法刪除”并回滾刪除操作。crea