实验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