SQL Server实用教程(第三版)电子工业出版社 实验部分

实验7存储过程和触发器的使用

1.目的与要求

（1）掌握存储过程的使用方法。

（2）掌握触发器的使用方法。

2.实验准备

（1）了解存储过程的使用方法。

（2）了解触发器的使用方法。

（3）了解inserted逻辑表和deleted逻辑表的使用。

（4）了解如何编写CRL存储过程与触发器。

3.实验内容

（1）存储过程

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

GO

CREATE PROCEDURE TEST@NU MBER1 int OUTPUT

AS

BEGIN DECLARE@ NUMBER2 INT;

SET NUMBER2=(SELECT COUNT(*) FROM Employees);

SET NUMBER1=NUMBER2;

END

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

DECLEAR@num int

EXEC TEST @num OUTPUT

SELECT@num

②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。

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

SQL Server实用教程(第三版)电子工业出版社 实验部分

AS

BEGIN

DECLARE@ SR1FLOAT,@SR2 FLOAT

SELECT @SR1=InCome-OutComeFROM 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。 USE YGGL

GO

CREATE PROCEDURE EmployeeAdd

(

@employeeid char(6),@name char(10),@education char(4),@birthday datetime,

@woekyear tinyint, @sex bit,@address char(40),@phonenumber char(12), @departmentID char(3)

)

AS

BEGIN

INSERT INTO Employees

VALUES(@employeeid,@name,@education,@birthday,@woekyear,

@sex,@address,@phonenumber,@departmentID)

END

SQL Server实用教程(第三版)电子工业出版社 实验部分

RETURN

GO

执行该存储过程： EXEC EmployeeAdd’990230’,,’刘朝’，‘本科’，‘840909’，2，1，‘武汉小洪山5号’，‘85465213’，‘3’

① 创建一个带有OUTPUT游标参数的存储过程，在Employees表中声明并打开一个游标 。 USE YGGL

GO 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

SQL Server实用教程(第三版)电子工业出版社 实验部分

② 创建存储过程,使用游标确定一个员工的实际收入是否排在前三名。结果为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

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

DECLARE@OK BIT

EXEC TOP_THREE’108991’,@OK OUTPUT

SELECT @OK

SQL Server实用教程(第三版)电子工业出版社 实验部分

【思考与练习】

a． 创建存储过程，要求当一个员工的工作年份大与6年时将其转到经理办公室工作。

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

c． 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。

d．使用命令方式修改存储过程的定义。

（2） 触发器

对于YGGL数据库，表Employees的DepartmentID列与表Departments的DepartmentID列应满足参照完整性规则，即

● 向Employees表添加记录时，该记录的“DepartmentID”字段值在Departments表中应该存在。

● 修改Departments表的“DepartmentID”字段值时，该字段在Employees表中的对应值也应该修改。

● 删除Departments表中的记录时，该记录的“DepartmentID”字段值在Employees表中对应的记录也应删除。

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

在查询分析器中输入各触发器的代码并执行。

① 向Employees表插入或修改一个记录时通过触发器检查记录的DpartmentID值在Dpartments表中是否存在，若不存在，则取消插入或修改操作。 USE YGGL

GO

CREATE TRIGGER EmployeesIns ON dbo.Employees

FOR INSERT,UPDATE

AS

BEGIN

IF((SELECT DepartmentID from inserted) NOT IN

(SELECT DepartmentID FROM DepartmentS))

SQL Server实用教程(第三版)电子工业出版社 实验部分

ROLLBACK

END

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

② 修改Dpartments表“DpartmentID”字段值时该字段在Employees表中对应的值也做相应修改 USE YGGL

GO

CREATE TRIGGER DepartmentUpdate ON dbo.Dep …… 此处隐藏：2681字，全部文档内容请下载后查看。喜欢就下载吧 ……