SQLServer实用教程（第三版）实验七答案.docVIP

200901501116 刘玉佩 存储过程和触发器的使用 存储过程 创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程 CREATE PROC TEST @NUMBER1 INT OUTPUT AS BEGIN DECLARE @NUMBER2 INT SET @NUMBER2=(SELECT COUNT(*) FROM Employees) SET @NUMBER1=@NUMBER2 END GO DECLARE @NUM INT EXEC TEST @NUM OUTPUT SELECT @NUM 创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否者输出1 CREATE PROC COMPA @ID1 CHAR(6),@ID2 CHAR(6),@BJ INT OUTPUT AS BEGIN DECLARE @SR1 FLOAT,@SR2 FLOAT SELECT @SR1=INCOME-OUTCOME FROM SALARY WHERE EMPLOYEEID=@ID1 SELECT @SR2=INCOME-OUTCOME FROM SALARY WHERE EMPLOYEEID=@ID2 IF @ID1@ID2 SET @BJ=0 ELSE SET @BJ=1 END DECLARE @BJ INT EXEC COMPA 000001,108991,@BJ OUTPUT SELECT @BJ 创建添加职员记录的储存过程EmployeeAdd CREATE PROC EmployeeAdd (@employeeid char(6),@name char(10),@education char(4),@birthday datetime, @workyear tinyint,@sex bit,@address char(40),@phonenumber char(12),@departmentid char(3) ) AS BEGIN INSERT INTO Employees VALUES(@employeeid,@name,@education,@birthday,@workyear, @sex,@address,@phonenumber,@departmentid) END GO EXEC EmployeeAdd990230,刘超,本科,840909,2,1,武汉3 创建一个带有OUTPUT游标参数的存储过程，在Employees表中生命并打开一个游标 CREATE PROC EM_CURSOR @EM_CURSOR CURSOR VARYING OUTPUT AS BEGIN SET @EM_CURSOR=CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM Employees OPEN @EM_CURSOR END GO DECLARE @MYCURSOR CURSOR EXEC EM_CURSOR @EM_CURSOR=@MYCURSOR OUTPUT FETCH NEXT FROM @MYCURSOR WHILE(@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @MYCURSOR END CLOSE @MYCURSOR DEALLOCATE @MYCURSOR GO 创建存储过程，使用游标确定一个员工的实际收入是否排在前三位。结果为1表示是，结果为0表示否 CREATE PROC TOP_THREE @EM_ID CHAR(6),@OK bit OUTPUT AS BEGIN DECLARE @X_EM_ID CHAR(6) DECLARE @ACT_IN INT,@SEQ INT DECLARE SALARY_DIS CURSOR FOR SELECT EmployeeID,INCOME-OUTCOME FROM SALARY ORDER BY INCOME-OUTCOME DESC SET @SEQ=0 SET @OK=0 OPEN SALARY_DIS FETCH SALARY_DIS INTO @X_EM_ID,@ACT_IN WHILE @SEQ3 AND @OK=0 BEGIN SET @SEQ=@SEQ+1 IF @X_EM_ID=@EM_ID SET @OK=1 FETCH SALARY_DIS INTO @X_EM_ID,@ACT_IN END CLOSE SALARY_DIS DEALLOCATE SALARY_DIS END GO DECLARE @OK BIT EXEC TOP_THREE 108991,