實(shí)驗(yàn)7-存儲(chǔ)過程和觸發(fā)器的使用

1、實(shí)驗(yàn) 7 存儲(chǔ)過程和觸發(fā)器的使用1. 存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程，使用Employees 表中的員工人數(shù)來初始化一個(gè)局部變量，并調(diào)用這個(gè)存儲(chǔ)過程USEYGGLGOCREATEPROCEDURETESTNU MBER int OUTPUTASBEGINDECLARENU MBER2 inTSETNU MBER2=(SELECTCOUNT(*) FROM Employees)SETNU MBER1=NU MBER2END創(chuàng)建存儲(chǔ)過程，比較兩個(gè)員工的實(shí)際收入，若前者比后者高就輸出0，否則輸出1BEGINDECLARESR1float , SR2FLOATSELECTSR1=InCome -OutCome

2、FROM Salary WHEREEmployeeID=ID1SELECTSR2=InCome -OutCome FROM Salary WHEREEmployeeID=ID2IF ID1ID2SETBJ=0ELSESETBJ=1END創(chuàng)建添加職員記錄的存儲(chǔ)過程EmployeeAddUSEYGGLGOCREATEPROCEDUREEmployeeAdd(employeeid char(6),name char(10),education char(4),birthday datetime ,woekyear tinyint , sex bit ,address char(40),phonenu

3、mberchar(12),departmentIDchar(3)ASBEGININSERTINTO EmployeesVALUES(employeeid ,name ,education ,birthday ,woekyear ,sex,address,phonenumber ,departmentID )ENDRETURNGO創(chuàng)建帶有OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過程，在Employees 表中聲明并打開游標(biāo)USEYGGLGOCREATEPROCEDUREem_cursor em_cursor cursor VARYINGOUTPUTASBEGINSETem_cursor =CURSOR FOR

4、WARD_ONLYSTATICFORSELECT* FROM EmployeesOPENem_cursorENDGO創(chuàng)建存儲(chǔ)過程，使用游標(biāo)確定一個(gè)員工的實(shí)際收入是否排在前三名，結(jié)果為1 表示是，結(jié)果為 0 表示否REATEPROCEDURETOP_THREEEM_ID char(6),OK bit OUTPUTASBEGINDECLAREX_EM_ID char(6)DECLAREACT_IN int ,SEQintDECLARESALARY_DIScursor FORSELECTEmployeeID,InCome_OutComeFROM SalaryORDERBYInCome_OutCome

5、 DESCSETSEQ=0SETOK=0OPENSALARY_DISFETCHSALARY_DISINTO X_EM_ID,ACT_INWHILESEQ3 AND OK=0BEGINSETSEQ=SEQ+1IF X_EM_ID=EM_IDSETOK=1FETCHSALARY_DISINTO X_EM_ID,ACT_INENDCLOSESALARY_DISDEALLOCATESALARY_DISEND2. 觸發(fā)器向 Employees 表插入或修改一個(gè)記錄時(shí)，通過觸發(fā)器檢查記錄的DpartmentID值在Dpartments 表中是否存在，若不存在，則取消插入或修改操作USEYGGLGOCREA

6、TETRIGGEREmployeesIns ONFORINSERT,UPDATEASBEGINIF(SELECTDepartmentIDfrom inserted )NOT IN(SELECTDepartmentIDFROMDepartmentS )ROLLBACKEND修改 Dpartments 表“ DpartmentID ”字段值時(shí)，該字段在Employees 表中對(duì)應(yīng)的值也做相應(yīng)修改USEYGGLUSEYGGLGOCREATETRIGGERDepartmentUpdateONFORUPDATEASBEGINUPDATEEmployeesSETDepartmentID =(SELECTD

7、epartmentIDFROMinsered )WHEREDepartmentID =(SELECTDepartmentIDFROMdeleted )ENDGO刪除 Dpartment 表中記錄的同時(shí)刪除該記錄“ DpartmentID ”字段值在Employees 表中對(duì)應(yīng)的記錄CREATETRIGGERDepartmentsDelateONFORDELETEASBEGINDELETEFROM EmployeesWHEREDepartmentID =(SELECT DepartmentIDFROM deleted )ENDGO創(chuàng)建 INSTEAD OF 觸發(fā)器，當(dāng)向Salary 表中插入記錄

8、時(shí)，先檢查EmployeeID 列上的值在Employees 中是否存在，如果在則執(zhí)行插入操作，如果不存在，則提示“員工號(hào)不存在”CREATETRIGGEREM_EXISTSON SalaryINSTEADOF INSERTASBEGINDECLAREEmployeeID char(6)SELECTEmployeeID =EmployeeIDFROM insertedIF(EmployeeIDIN(SELECTEmployeeID FROMEmployee)INSERTINTO Salary SELECT* FROM insertedELSEPRINT員工號(hào)不存在END創(chuàng)建 DDL 觸發(fā)器，當(dāng)刪除