SQLServer实用教程（第三版）实验七答案.doc
文本预览下载声明
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,
显示全部