1、課程名稱數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)實(shí)驗(yàn)名稱存儲(chǔ)過(guò)程和觸發(fā)器的使用成績(jī)學(xué)號(hào)姓名班級(jí)日期14.11.25實(shí)驗(yàn)?zāi)康模?. 掌握存儲(chǔ)過(guò)程的使用方法；2. 掌握觸發(fā)器的實(shí)現(xiàn)方法；實(shí)驗(yàn)平臺(tái)：利用RDBMS（ SQL Server 2008）及其交互查詢工具（查詢分析器）來(lái)操作T-SQL 語(yǔ)言；實(shí)驗(yàn)內(nèi)容：1. 存儲(chǔ)過(guò)程（ 1） 創(chuàng)建存儲(chǔ)過(guò)程，要求當(dāng)一個(gè)員工的工作年份大于6 年時(shí)將其轉(zhuǎn)到經(jīng)理辦公室工作。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','經(jīng)理辦公室 '

3、;（ 2） 創(chuàng)建存儲(chǔ)過(guò)程，根據(jù)每個(gè)員工的學(xué)歷將收入提高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） 創(chuàng)建存儲(chǔ)過(guò)程，使用游標(biāo)計(jì)算本科及以上學(xué)歷的員工在總員工數(shù)中的比例。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+ ' 占總?cè)藬?shù)比例

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

7、1） 對(duì)于 YGGL數(shù)據(jù)庫(kù)，表 Employees 的 Employeeid 列與表 Salary 的Employeeid列應(yīng)滿足參照完整性規(guī)則，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表間的參照完整性。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）當(dāng)修改表Employees 時(shí)，若將Employees 表中員工的工作時(shí)間增加1年，則將收

9、入增加500 ，若增加2 年則增加1000 ，依次增加。若工作時(shí)間減少則無(wú)變化。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）創(chuàng)建 UPDATE觸發(fā)器，當(dāng)Salary表中 InCome 值增加 500時(shí)， outCome值則增加 50 。createtriggersa_incomeonSalaryforupdate as beginif (selectincomefrominserted)-(selectincomefromdeleted)= 500 )updateSalarysetoutcome = outcome+ 50whereenployeeid=( selectenployeeidfrominser

11、ted)end selectincome, outcomefromSalarywhereenployeeid= '000001'（ 5） 創(chuàng)建 INSTEAD OF 觸發(fā)器，實(shí)現(xiàn)向不可更新視圖插入數(shù)據(jù)。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）創(chuàng)建DDL 觸發(fā)器，當(dāng)刪除數(shù)據(jù)庫(kù)時(shí)，提示“無(wú)法刪除”并回滾刪除操作。crea