SQL实验七存储过程和触发器的使用

(二〇一五年五月《数据库原理及应用》实验报告学校代码：10128学号：201220905048题目：存储过程和触发器的使用学生姓名：孙跃学院：理学院系别：数学系专业：信息与计算科学班级：信计12-2任课教师：侯睿2一、实验目的1.掌握存储过程的使用方法；2.掌握触发器的使用方法。二、实验内容1.存储过程；2.触发器。三、实验程序及结果1、存储过程(1)创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。USEYGGLGOCREATEPROCEDURETEST@NUMBER1intOUTPUTASBEGINDECLARE@NUMBER2intSET@NUMBER2=(SELECTCOUNT(*)FROMEmployees)SET@NUMBER1=@NUMBER2ENDGOUSEYGGLGODECLARE@numintEXECTEST@numOUTPUTSELECT@num(2)创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。USEYGGLGOCREATEPROCEDURECOMPA@ID1CHAR(6),@ID2CHAR(6),@BJINTOUTPUTASBEGINDECLARE@SR1FLOAT,@SR2FLOATSELECT@SR1=InCome-OutComeFROMSalary3WHEREEmployeeID=@ID1SELECT@SR2=InCome-OutComeFROMSalaryWHEREEmployeeID=@ID2IF@ID1@ID2SET@BJ=0ELSESET@BJ=1ENDUSEYGGLGODECLARE@BJintEXECCOMPA'000001','108991',@BJOUTPUTSELECT@BJ(3)创建添加职员记录的存储过程EmployeeADD。USEYGGLGOCREATEPROCEDUREEmployeeAdd(@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)ENDRETURNGO4USEYGGLGOEXECEmployeeAdd'990230','刘朝','本科','840909',2,1,'武汉小洪山号','85465213','3'(4)创建一个带有OUTPUT游标参数的存储过程，在Employees表中声明并打开一个游标。USEYGGLGOCREATEPROCEDUREem_cursor@em_cursorcursorVARYINGOUTPUTASBEGINSET@em_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMEmployeesOPEN@em_cursorENDGO声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录：USEYGGLGODECLARE@MYCURSORCURSOR5EXECem_cursor@em_cursor=@MyCursorOUTPUTFETCHNEXTFROM@MyCursorWHILE(@@FETCH_STATUS=0)BEGINFETCHNEXTFROM@MyCursorENDCLOSE@MyCursorDEALLOCATE@MyCursorGO(5)创建一个存储过程，使用游标确定一个员工的实际收入是否排在前三名。结果为1表示是，结果为0表示否。USEYGGLGOCREATEPROCEDURETOP_THREE@EM_IDchar(6),@OKbitOUTPUTASBEGINDECLARE@X_EM_IDchar(6)DECLARE@ACT_INint,@SEQintDECLARESALARY_DIScursorFOR6SELECTEmployeeID,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_DISEND执行该存储过程，并查看结果：USEYGGLGODECLARE@OKbitEXECTOP_THREE'108991',@OKOUTPUTSELECT@OK2、触发器(1)向Employees表插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Department表中是否存在，若不存在，则取消插入或修改操作。USEYGGLGOCREATETRIGGEREmployeesInsONdbo.EmployeesFORINSERT,UPDATEASBEGINIF((SELECTDepartmentIDfrominserted)NOTIN(SELECTDepartmentIDFROMDepartment))ROLLBACKEND7向Employees表插入或修改一行记录，查看结果(2)修改Department表“DpartmentID”字段值时,该字段在Employees表中对应的值也做相应修改。USEYGGLGOCREATETRIGGERDepartmentUpdateONdbo.DepartmentFORUPDATEASBEGINUPDATEEmployeesSETDepartmentID=(SELECTDepartmentIDFROMinsered)WHEREDepartmentID=(SELECTDepartmentIDFROMdeleted)ENDGO(3)删除Department表中记录的同时删除该记录“DepartmentID”字段值在Employees表中对应的记录。USEYGGLGOCREATETRIGGERDepartmentsDelateONdbo.DepartmentFORDELETEASBEGINDELETEFROMEmployeesWHEREDepartmentID=(SELECTDepartmentIDFROMdeleted)ENDGO(4)创建INSTEADOF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”。USEYGGLGO8CREATETRIGGEREM_EXISTSONSalaryINSTEADOFINSERTASBEGINDECLARE@EmployeeIDchar(6)SELECT@EmployeeID=EmployeeIDFROMinsertedIF(@EmployeeIDIN(SELECTEmployeeIDFROMEmployees))INSERTINTOSalarySELECT*FROMinsertedELSEPRINT'员工号不存在'END向Salary表中插入一行记录查看效果：USEYGGLGOINSERTINTOSalaryVALUES('111111',2500.3,123.2)(5)创建DDL触发器,当删除YGGL数据库的一个表时,提示“不能删除表”,并回滚删除表的操作。USEYGGLGOCREATETRIGGERtable_deleteONDATABASEAFTERDROP_TABLEASPRINT'不能删除该表'ROLLBACKTRANSACTION四、实验总结本次上机实验，通过对存储过程和触发器两个内容的实际操作，让我对存储过程的使用方法和触发器的使用方法有了更好的掌握，同时也对课堂所学知识进行了巩固和提高，我觉得在上机操作中还应该耐心细致，认真思考，把出错的可能性降到最低，同时也让自己的实际操作水平得以提高。