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

admin管理员组

文章数量:1534205

2024年6月4日发(作者：)

一、存储过程

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

这个存储过程。

CREATE PROCEDURE TEST @NUMBER1 INT OUTPUT

AS

BEGIN

DECLARE @NUMBER2 INT

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 @ID1 char(6),@ID2 char(6),@BJ int OUTPUT

AS

BEGIN

DECLARE @SR1 float,@SR2 float

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

本文标签： 过程记录删除表中游标