实验8 存储过程和触发器

• 实验目的
  1. 熟练掌握使用命令方式创建不带参数以及带参数的存储过程的方法。
  2. 熟练掌握执行存储过程的方法。
  3. 熟练掌握触发器的创建方法。
• 实验内容

（1）创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。代码如下:

CREATE PROCEDURE TEST @NU_MBER1 INT OUTPUT

AS

BEGIN

  DECLARE @NU_MBER2 INT

  SET @NU_MBER2=(SELECT COUNT(*) FROM Employees)

  SET @NU_MBER1= @NU_MBER2

END

GO 

执行该存储过程，并查看结果：

DECLARE @num INT

EXEC TEST @num OUTPUT

SELECT '总人数'=@num

(2)创建存储过程，比较两个员工的实际收入，若前者高就输出0，否则输出1。代码如下：

CREATE PROCEDURE COMPA @ID1 CHAR(6),@ID2 CHAR(6),@BJ INT OUTPUT 

AS  

BEGIN 

DECLARE @SR1 FLOAT 

DECLARE @SR2 FLOAT 

SELECT @SR1 =InCome-OutCome FROM Salary WHERE EmployeesID=@ID1 

SELECT @SR2 =InCome-OutCome FROM Salary WHERE EmployeesID=@ID2  

IF  @SR1>@SR2  

SET @BJ=0

ELSE 

SET @BJ=1

END

执行该存储过程，并查看结果：

DECLARE @BJ INT

EXEC compa '000001','108991',@BJ OUTPUT

SELECT '比较结果'=@BJ

 (3)创建添加职员记录的存储过程EmployeeAdd。代码如下：

CREATE PROCEDURE EmployeeAdd

@employeeid char(6),@employeename 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,@employeename,@education,@birthday,@workyear,@sex,@address,@phonenumber,@departmentID)

END

GO

执行该存储过程：

EXEC EmployeeAdd '990230','刘朝','本科','840909',2,1,'武汉小洪山号','85465213','3' 

（4）对于YGGL数据库，表Employees的DepartmentID列与表Departments的DepartmentID列应满足参照完整性，即：

1. 向Employees表添加记录时，该记录的“DepartmentID”字段值在Departments表中应存在。
2. 修改Departments表的“DepartmentID”字段值时，该字段在Employees表中的对应值也应修改。
3. 删除Departments表中的记录时，该记录的“DepartmentID”字段值在Employees表中的对应值也应删除。

对于上述参照完整性规则，在此通过触发器实现。

向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

GO

向Employees表插入或修改一行记录，查看效果。

（5）修改Departments表“DepartmentID”字段值时，该字段在Employees表中的对应值也做相应修改。代码如下：

USE YGGL

GO

CREATE TRIGGER DepartmentsUpdate ON dbo.Departments

FOR UPDATE

AS

BEGIN

UPDATE Employees

SET DepartmentID=(SELECT DepartmentID FROM inserted)

WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

END

GO

（6）删除Departments表中记录的同时删除该记录“DepartmentID”字段值在Employees表中对应的记录。代码如下：

CREATE TRIGGER DepartmentsDelete ON dbo.Departments

FOR DELETE

AS

BEGIN

DELETE FROM Employees

WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

END

GO

三、思考与练习

（1）创建存储过程，要求当一个员工的工作年份大于等于3年时将其转到经理办公室工作。

（2）创建存储过程，根据每个员工的学历将收入提高500元。

（3）创建一个带参数的存储过程，根据员工编号查询三个人的收入情况。

（4）对于YGGL数据库，表Employees的EmployeesID列与表Salary的EmployeesID列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。

（5）创建UPDATE触发器，当Salary表中InCome值增加500时，OuCome值则增加50。