实验7-存储过程和触发器的使用

1、实验 7 存储过程和触发器的使用1. 存储过程创建存储过程，使用Employees 表中的员工人数来初始化一个局部变量，并调用这个存储过程USEYGGLGOCREATEPROCEDURETESTNU MBER int OUTPUTASBEGINDECLARENU MBER2 inTSETNU MBER2=(SELECTCOUNT(*) FROM Employees)SETNU MBER1=NU MBER2END创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1BEGINDECLARESR1float , SR2FLOATSELECTSR1=InCome -OutCome

2、FROM Salary WHEREEmployeeID=ID1SELECTSR2=InCome -OutCome FROM Salary WHEREEmployeeID=ID2IF ID1ID2SETBJ=0ELSESETBJ=1END创建添加职员记录的存储过程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创建带有OUTPUT游标参数的存储过程，在Employees 表中声明并打开游标USEYGGLGOCREATEPROCEDUREem_cursor em_cursor cursor VARYINGOUTPUTASBEGINSETem_cursor =CURSOR FOR

4、WARD_ONLYSTATICFORSELECT* FROM EmployeesOPENem_cursorENDGO创建存储过程，使用游标确定一个员工的实际收入是否排在前三名，结果为1 表示是，结果为 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. 触发器向 Employees 表插入或修改一个记录时，通过触发器检查记录的DpartmentID值在Dpartments 表中是否存在，若不存在，则取消插入或修改操作USEYGGLGOCREA

6、TETRIGGEREmployeesIns ONFORINSERT,UPDATEASBEGINIF(SELECTDepartmentIDfrom inserted )NOT IN(SELECTDepartmentIDFROMDepartmentS )ROLLBACKEND修改 Dpartments 表“ DpartmentID ”字段值时，该字段在Employees 表中对应的值也做相应修改USEYGGLUSEYGGLGOCREATETRIGGERDepartmentUpdateONFORUPDATEASBEGINUPDATEEmployeesSETDepartmentID =(SELECTD

7、epartmentIDFROMinsered )WHEREDepartmentID =(SELECTDepartmentIDFROMdeleted )ENDGO删除 Dpartment 表中记录的同时删除该记录“ DpartmentID ”字段值在Employees 表中对应的记录CREATETRIGGERDepartmentsDelateONFORDELETEASBEGINDELETEFROM EmployeesWHEREDepartmentID =(SELECT DepartmentIDFROM deleted )ENDGO创建 INSTEAD OF 触发器，当向Salary 表中插入记录

8、时，先检查EmployeeID 列上的值在Employees 中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”CREATETRIGGEREM_EXISTSON SalaryINSTEADOF INSERTASBEGINDECLAREEmployeeID char(6)SELECTEmployeeID =EmployeeIDFROM insertedIF(EmployeeIDIN(SELECTEmployeeID FROMEmployee)INSERTINTO Salary SELECT* FROM insertedELSEPRINT员工号不存在END创建 DDL 触发器，当删除