數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)六

1、課程名稱(chēng)數(shù)據(jù)庫(kù)技術(shù)實(shí)驗(yàn)名稱(chēng)存儲(chǔ)過(guò)程和觸發(fā)器的使用實(shí)驗(yàn)成績(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（SQLServer2008）及其交互查詢(xún)工具（查詢(xún)分析器）來(lái)操作TSQL語(yǔ)言；實(shí)驗(yàn)內(nèi)容：1.存儲(chǔ)過(guò)程（1）創(chuàng)建存儲(chǔ)過(guò)程，要求當(dāng)一個(gè)員工的工作年份大于6年時(shí)將其轉(zhuǎn)到經(jīng)理辦公室工作。alterprocedureyuangong_infol@EmployeeID(6)

2、@namen(20)asbegindeclare@yearintset@year=(WkYearfromEmployeeswhereEmployeeID=@EmployeeID)declare@DepartmentID(3)set@DepartmentID=(DepartmentIDfromDepartmentswhereDepartmentName=@name)if(@year6)updateEmployeessetDepartmen

3、tID=@DepartmentIDwhereEmployeeID=@EmployeeIDEndexecdbo.yuangong_infol000000經(jīng)理辦公室fromEmployeesopenmycursfetchnextfrommycursinto@edu@part_count@all_countwhile@@FETCH_STATUS=0beginprint@edu占總?cè)藬?shù)比例：convert(var(100)convert(num

4、eric(382)@part_count1.0@all_count100))%fetchnextfrommycursinto@edu@part_count@all_countendclosemycusdeallocatemycurs（4）使用命令方式修改及刪除一個(gè)存儲(chǔ)過(guò)程。ifexists(wkyearfromEmployeeswherewkyear=3)procedurewkyear2.觸發(fā)器（1）對(duì)于YGGL數(shù)據(jù)庫(kù)，表Employe

5、es的Employeeid列與表Salary的Employeeid列應(yīng)滿(mǎn)足參照完整性規(guī)則，請(qǐng)用觸發(fā)器實(shí)現(xiàn)兩個(gè)表間的參照完整性。createtriggerSalaryins0onSalaryfupdateasbeginif(employeeidfromed)notin(EmployeeIDfromEmployees)rollbackendcreatetriggerEmployeesupdate0ondbo.Employeesfupdate