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

实验7存储过程和触发器的使用1.存储过程①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程USEYGGLGOCREATEPROCEDURETEST@NUMBERintOUTPUTASBEGINDECLARE@NUMBER2inTSET@NUMBER2=(SELECTCOUNT(*)FROMEmployees)SET@NUMBER1=@NUMBER2END②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1BEGINDECLARE@SR1float,@SR2FLOATSELECT@SR1=InCome-OutComeFROMSalaryWHEREEmployeeID=@ID1SELECT@SR2=InCome-OutComeFROMSalaryWHEREEmployeeID=@ID2IF@ID1@ID2SET@BJ=0ELSESET@BJ=1END③创建添加职员记录的存储过程EmployeeAddUSEYGGLGOCREATEPROCEDUREEmployeeAdd(@employeeidchar(6),@namechar(10),@educationchar(4),@birthdaydatetime,@woekyeartinyint,@sexbit,@addresschar(40),@phonenumberchar(12),@departmentIDchar(3))ASBEGININSERTINTOEmployeesVALUES(@employeeid,@name,@education,@birthday,@woekyear,@sex,@address,@phonenumber,@departmentID)ENDRETURNGO④创建带有OUTPUT游标参数的存储过程，在Employees表中声明并打开游标USEYGGLGOCREATEPROCEDUREem_cursor@em_cursorcursorVARYINGOUTPUTASBEGINSET@em_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMEmployeesOPEN@em_cursorENDGO⑤创建存储过程，使用游标确定一个员工的实际收入是否排在前三名，结果为1表示是，结果为0表示否REATEPROCEDURETOP_THREE@EM_IDchar(6),@OKbitOUTPUTASBEGINDECLARE@X_EM_IDchar(6)DECLARE@ACT_INint,@SEQintDECLARESALARY_DIScursorFORSELECTEmployeeID,InCome_OutComeFROMSalaryORDERBYInCome_OutComeDESCSET@SEQ=0SET@OK=0OPENSALARY_DISFETCHSALARY_DISINTO@X_EM_ID,@ACT_INWHILE@SEQ3AND@OK=0BEGINSET@SEQ=@SEQ+1IF@X_EM_ID=@EM_IDSET@OK=1FETCHSALARY_DISINTO@X_EM_ID,@ACT_INENDCLOSESALARY_DISDEALLOCATESALARY_DISEND2.触发器①向Employees表插入或修改一个记录时，通过触发器检查记录的DpartmentID值在Dpartments表中是否存在，若不存在，则取消插入或修改操作USEYGGLGOCREATETRIGGEREmployeesInsONdbo.EmployeesFORINSERT,UPDATEASBEGINIF((SELECTDepartmentIDfrominserted)NOTIN(SELECTDepartmentIDFROMDepartmentS))ROLLBACKEND②修改Dpartments表“DpartmentID”字段值时，该字段在Employees表中对应的值也做相应修改USEYGGLUSEYGGLGOCREATETRIGGERDepartmentUpdateONdbo.DepartmentsFORUPDATEASBEGINUPDATEEmployeesSETDepartmentID=(SELECTDepartmentIDFROMinsered)WHEREDepartmentID=(SELECTDepartmentIDFROMdeleted)ENDGO③删除Dpartment表中记录的同时删除该记录“DpartmentID”字段值在Employees表中对应的记录CREATETRIGGERDepartmentsDelateONdbo.DepartmentsFORDELETEASBEGINDELETEFROMEmployeesWHEREDepartmentID=(SELECTDepartmentIDFROMdeleted)ENDGO④创建INSTEADOF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”CREATETRIGGEREM_EXISTSONSalaryINSTEADOFINSERTASBEGINDECLARE@EmployeeIDchar(6)SELECT@EmployeeID=EmployeeIDFROMinsertedIF(@EmployeeIDIN(SELECTEmployeeIDFROMEmployee))INSERTINTOSalarySELECT*FROMinsertedELSEPRINT'员工号不存在'END⑤创建DDL触发器，当删除YGGLXJ数据库的一个表时，提示“不能删除表”，并回滚删除表的操作USEYGGLGOCREATETRIGGERtable_deleteONDATABASEAFTERDROP_TABLEASPRINT'不能删除该表'ROLLBACKTRANSACTION