内容发布更新时间 : 2024/12/23 13:30:45星期一 下面是文章的全部内容请认真阅读。

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

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

储过程

USE YGGL GO

CREATE PROCEDURE TEST @NU MBER int OUTPUT AS BEGIN

DECLARE @NU MBER2 inT

SET @NU MBER2=(SELECT COUNT(*) FROM Employees) SET @NU MBER1=@NU MBER2 END

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

BEGIN

DECLARE @SR1 float, @SR2 FLOAT

SELECT @SR1=InCome-OutCome 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

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

INSERT INTO Employees

VALUES(@employeeid,@name ,@education,@birthday,@woekyear, @sex,@address,@phonenumber,@departmentID) END RETURN GO

AS

④ 创建带有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