资源描述：
《SQLServer实用教程（第三版）实验七答案》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
1、200901501116刘玉佩存储过程和触发器的使用（1）存储过程1.创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程CREATEPROCTEST@NUMBER1INTOUTPUTASBEGINDECLARE@NUMBER2INTSET@NUMBER2=(SELECTCOUNT(*)FROMEmployees)SET@NUMBER1=@NUMBER2ENDGODECLARE@NUMINTEXECTEST@NUMOUTPUTSELECT@NUM2.创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否者输出1CREA
2、TEPROCCOMPA@ID1CHAR(6),@ID2CHAR(6),@BJINTOUTPUTASBEGINDECLARE@SR1FLOAT,@SR2FLOATSELECT@SR1=INCOME-OUTCOMEFROMSALARYWHEREEMPLOYEEID=@ID1SELECT@SR2=INCOME-OUTCOMEFROMSALARYWHEREEMPLOYEEID=@ID2IF@ID1>@ID2SET@BJ=0ELSESET@BJ=1ENDDECLARE@BJINTEXECCOMPA'000001','108991',@BJOUTPUTSELECT@BJ3.创建
3、添加职员记录的储存过程EmployeeAddCREATEPROCEmployeeAdd(@employeeidchar(6),@namechar(10),@educationchar(4),@birthdaydatetime,@workyeartinyint,@sexbit,@addresschar(40),@phonenumberchar(12),@departmentidchar(3))ASBEGININSERTINTOEmployeesVALUES(@employeeid,@name,@education,@birthday,@workyear,@sex,@a
4、ddress,@phonenumber,@departmentid)ENDGOEXECEmployeeAdd'990230','刘超','本科','840909',2,1,'武汉','85465213','3'创建一个带有OUTPUT游标参数的存储过程，在Employees表中生命并打开一个游标CREATEPROCEM_CURSOR@EM_CURSORCURSORVARYINGOUTPUTASBEGINSET@EM_CURSOR=CURSORFORWARD_ONLYSTATICFORSELECT*FROMEmployeesOPEN@EM_CURSORENDGODEC
5、LARE@MYCURSORCURSOREXECEM_CURSOR@EM_CURSOR=@MYCURSOROUTPUTFETCHNEXTFROM@MYCURSORWHILE(@@FETCH_STATUS=0)BEGINFETCHNEXTFROM@MYCURSORENDCLOSE@MYCURSORDEALLOCATE@MYCURSORGO创建存储过程，使用游标确定一个员工的实际收入是否排在前三位。结果为1表示是，结果为0表示否CREATEPROCTOP_THREE@EM_IDCHAR(6),@OKbitOUTPUTASBEGINDECLARE@X_EM_IDCHAR(6
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=@SEQ+1IF@X_EM_ID=@EM_IDSET@OK=1FETCHSALARY_DISINTO@X_EM_ID,@ACT_INENDC
7、LOSESALARY_DISDEALLOCATESALARY_DISENDGODECLARE@OKBITEXECTOP_THREE'108991',@OKOUTPUTSELECT@OK创建存储过程，要求当一个员工的年份大于6年时将其转到经理办公室工作CREATEPROCYG@EMCHAR(6)ASBEGINIF(SELECTWorkYearFROMEmployeesWHEREEmployeeID=@EM)>6UPDATEEmployeesSETDepartmentID='3'WHEREEmployeeID=@EMENDGOEXECYG'000001'创建存储过程
