SQLSERVER2008实用教程实验参考答案(实验7)

一、存储过程
1. 创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

CREATE PROCEDURE TEST@NUMBER1INT OUTPUT
AS
BEGIN
DECLARE@NUMBER2INT
SET@NUMBER2=(SELECT COUNT(*)FROM Employees)
SET@NUMBER1=@NUMBER2
END
执行该存储过程，查看结果。

DECLARE@num INT
EXEC TEST@num OUTPUT
SELECT@num
2. 创建存储过程，比较两个员工的实际收入，若前者比后者高就输出1，否则就输出0。

CREATE PROCEDURE COMPA@ID1char(6),@ID2char(6),@BJ int OUTPUT
AS
BEGIN
DECLARE@SR1float,@SR2float
SET@SR1=(SELECT InCome-OutCome FROM Salary WHERE EmployeeID=@ID1) SET@SR2=(SELECT InCome-OutCome FROM Salary WHERE EmployeeID=@ID2) IF@SR1>@SR2
SET@BJ=1
ELSE
SET@BJ=0
END
执行该存储过程，查看结果。

DECLARE@BJ int
EXEC COMPA'504209','302566',@BJ OUTPUT
SELECT@BJ
3. 创建添加职员记录的存储过程EmployeeAdd。

CREATE PROCEDURE 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
RETURN
GO
执行该存储过程。

EXEC EmployeeAdd'999992','小莫','硕士','1984-09-09',1,2,'武汉路','66666666','3'
4. 创建一个带有OUTPUT游标参数的存储过程，在Employees表中声明并打开一个游标。

CREATE PROCEDURE 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
5. 创建存储过程，使用游标确定一个员工的实际收入是否排在前三名。

结果为1表示是，结果为0表示否。

CREATE PROCEDURE 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@SEQ<3 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
执行该存储过程，并查看结果。

--先执行SELECT * FROM Salary ORDER BY InCome-OutCome查看排序结果
DECLARE@OK bit
EXEC TOP_THREE'102201',@OK OUTPUT
SELECT@OK
二、触发器
1. 向Employees表插入或修改一个记录时，通过触发器检查记录的DepartmentID值在Departments表中是否存在，若不存在，则取消插入或修改操作。

CREATE TRIGGER EmployeeIns ON Employees
FOR INSERT,UPDATE
AS
BEGIN
IF(
(SELECT DepartmentID FROM inserted)NOT IN(SELECT DepartmentID FROM Departments)
)
ROLLBACK
END
插入或修改数据，查看效果。

验证之前需要删除之前的实验中建立的外键和约束关系，以确认触发器的有效性
2. 修改Departments表“DepartmentID”字段值时，该字段在Employees表中的对应值也做相应修改。

CREATE TRIGGER DepartmentUpdate ON Departments
FOR UPDATE
AS
BEGIN
UPDATE Employees
SET DepartmentID=(SELECT DepartmentID FROM inserted)
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
注意：需要先删除1.中建立的触发器，否则会报错。

3. 删除Departments表中记录的同时删除该记录“DepartmentID”字段值在Employees表中
对应的记录。

CREATE TRIGGER DepartmentsDelete ON Departments
FOR DELETE
AS
BEGIN
DELETE FROM Employees
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
GO
4. 创建INSTEAD OF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果存在则执行插入操作，如果不存在则提示“员工不存在”。

CREATE TRIGGER EM_EXISTS ON Salary
INSTEAD OF INSERT
AS
BEGIN
DECLARE@EmployeeID char(6)
SET@EmployeeID=(SELECT EmployeeID FROM inserted)
IF (@EmployeeID IN(SELECT EmployeeID FROM Employees))
INSERT INTO Salary SELECT*FROM inserted
ELSE
PRINT'员工号不存在'
END
向Salary表中插入数据来查看效果。

注意：需要先删除原先建立的外键，以确定是触发器在起作用
INSERT INTO Salary VALUES('222226',2000,12);
5. 创建DDL（数据定义语言）触发器，当删除YGGL数据库的一个表时，提示“不能删除表”，并回滚删除表的操作。

CREATE TRIGGER table_delete ON DATABASE
AFTER DROP_TABLE
AS
PRINT'不能删除该表'
ROLLBACK TRANSACTION。