SQL实验七：存储过程和触发器的使用

(

二 〇 一 五 年 五 月

《数据库原理及应用》实验报告

学校代码： 10128 学 号： 201220905048

题 目：存储过程和触发器的使用 学生姓名：孙跃 学 院：理学院 系

别：数学系

专 业：信息与计算科学 班 级：信计12-2 任课教师：侯睿

一、实验目的

1.掌握存储过程的使用方法；

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

二、实验内容

1.存储过程；

2.触发器。

三、实验程序及结果

1、存储过程

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

USE YGGL

GO

CREATE PROCEDURE TEST @NUMBER1 int OUTPUT

AS

BEGIN

DECLARE @NUMBER2 int

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

SET @NUMBER1=@NUMBER2

END

GO

USE YGGL

GO

DECLARE @num int

EXEC TEST @num OUTPUT

SELECT @num

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

GO

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

AS

BEGIN

DECLARE @SR1 FLOAT,@SR2 FLOAT

SELECT

@SR1=InCome-OutCome

FROM

Salary

EmployeeID=@ID1

SELECT @SR2=InCome-OutCome FROM Salary WHERE EmployeeID=@ID2 IF @ID1>@ID2

SET @BJ=0

ELSE

SET @BJ=1

END

USE YGGL

GO

DECLARE @BJ int

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

SELECT @BJ

(3)创建添加职员记录的存储过程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

RETURN

GO

GO

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

'85465213','3'

(4)创建一个带有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

声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录：

USE YGGL

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表示否。

USE YGGL

GO

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

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

USE YGGL

GO

DECLARE @OK bit

EXEC TOP_THREE '108991',@OK OUTPUT

SELECT @OK

2、触发器

(1) 向Employees表插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Department表中是否存在，若不存在，则取消插入或修改操作。

USE YGGL

GO

CREATE TRIGGER EmployeesIns ON dbo.Employees

FOR INSERT,UPDATE

AS

BEGIN

IF((SELECT DepartmentID from inserted)NOT IN

(SELECT DepartmentID FROM Department))

ROLLBACK

END

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

(2) 修改Department表“DpartmentID”字段值时,该字段在Employees表中对应的值也做相应修改。

USE YGGL

GO

CREATE TRIGGER DepartmentUpdate ON dbo.Department

FOR UPDATE

AS

BEGIN

UPDATE Employees

SET DepartmentID=(SELECT DepartmentID FROM insered)

WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

END

GO

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

USE YGGL

GO

CREATE TRIGGER DepartmentsDelate ON dbo.Department

FOR DELETE

AS

BEGIN

DELETE FROM Employees

WHERE DepartmentID=(SELECT DepartmentID FROM deleted)

END

GO

(4) 创建INSTEAD OF 触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”。

USE YGGL

GO

CREATE TRIGGER EM_EXISTS ON Salary

INSTEAD OF INSERT

AS

BEGIN

DECLARE @EmployeeID char(6)

SELECT @EmployeeID=EmployeeID

FROM inserted

IF(@EmployeeID IN(SELECT EmployeeID FROM Employees))

INSERT INTO Salary SELECT*FROM inserted

ELSE

PRINT'员工号不存在'

END

向Salary表中插入一行记录查看效果：

USE YGGL

GO

INSERT INTO Salary VALUES('111111',2500.3,123.2)

(5)创建DDL触发器,当删除YGGL数据库的一个表时,提示“不能删除表”,并回滚删除表的操作。

USE YGGL

GO

CREATE TRIGGER table_delete

ON DATABASE

AFTER DROP_TABLE

AS

PRINT'不能删除该表'

ROLLBACK TRANSACTION

四、实验总结

本次上机实验，通过对存储过程和触发器两个内容的实际操作，让我对存储过程的使用方法和触发器的使用方法有了更好的掌握，同时也对课堂所学知识进行了巩固和提高，我觉得在上机操作中还应该耐心细致，认真思考，把出错的可能性降到最低，同时也让自己的实际操作水平得以提高。