资源描述：
《实验7-存储过程和触发器的使用.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、实验7存储过程和触发器的使用1.存储过程①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程USEYGGLGOCREATEPROCEDURETEST@NUMBERintOUTPUTASBEGINDECLARE@NUMBER2inTSET@NUMBER2=(SELECTCOUNT(*)FROMEmployees)SET@NUMBER1=@NUMBER2END②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1BEGINDECLARE@SR1float,
2、@SR2FLOATSELECT@SR1=InCome-OutComeFROMSalaryWHEREEmployeeID=@ID1SELECT@SR2=InCome-OutComeFROMSalaryWHEREEmployeeID=@ID2IF@ID1>@ID2SET@BJ=0ELSESET@BJ=1END①创建添加职员记录的存储过程EmployeeAddUSEYGGLGOCREATEPROCEDUREEmployeeAdd(@employeeidchar(6),@namechar(10),@educationc
3、har(4),@birthdaydatetime,@woekyeartinyint,@sexbit,@addresschar(40),@phonenumberchar(12),@departmentIDchar(3))ASBEGININSERTINTOEmployeesVALUES(@employeeid,@name,@education,@birthday,@woekyear,@sex,@address,@phonenumber,@departmentID)ENDRETURNGO①创建带有OUTPUT游标参数
4、的存储过程，在Employees表中声明并打开游标USEYGGLGOCREATEPROCEDUREem_cursor@em_cursorcursorVARYINGOUTPUTASBEGINSET@em_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMEmployeesOPEN@em_cursorENDGO①创建存储过程，使用游标确定一个员工的实际收入是否排在前三名，结果为1表示是，结果为0表示否REATEPROCEDURETOP_THREE@EM_IDchar(6),@
5、OKbitOUTPUTASBEGINDECLARE@X_EM_IDchar(6)DECLARE@ACT_INint,@SEQintDECLARESALARY_DIScursorFORSELECTEmployeeID,InCome_OutComeFROMSalaryORDERBYInCome_OutComeDESCSET@SEQ=0SET@OK=0OPENSALARY_DISFETCHSALARY_DISINTO@X_EM_ID,@ACT_INWHILE@SEQ<3AND@OK=0BEGINSET@SEQ=@SE
6、Q+1IF@X_EM_ID=@EM_IDSET@OK=1FETCHSALARY_DISINTO@X_EM_ID,@ACT_INENDCLOSESALARY_DISDEALLOCATESALARY_DISEND1.触发器①向Employees表插入或修改一个记录时，通过触发器检查记录的DpartmentID值在Dpartments表中是否存在，若不存在，则取消插入或修改操作USEYGGLGOCREATETRIGGEREmployeesInsONdbo.EmployeesFORINSERT,UPDATEASBEGI
7、NIF((SELECTDepartmentIDfrominserted)NOTIN(SELECTDepartmentIDFROMDepartmentS))ROLLBACKEND①修改Dpartments表“DpartmentID”字段值时，该字段在Employees表中对应的值也做相应修改USEYGGLUSEYGGLGOCREATETRIGGERDepartmentUpdateONdbo.DepartmentsFORUPDATEASBEGINUPDATEEmployeesSETDepartmentID=(SEL
8、ECTDepartmentIDFROMinsered)WHEREDepartmentID=(SELECTDepartmentIDFROMdeleted)ENDGO①删除Dpartment表中记录的同时删除该记录“DpartmentID”字段值在Employees表中对应的记录CREATETRIGGERDepartmentsDelateONdbo.DepartmentsFORDELETE
