一、存储过程
1. 创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。
CREATE PROCEDURE TEST @NUMBER1 INT OUTPUT AS BEGIN END
DECLARE @NUMBER2 INT
SET @NUMBER2=(SELECT COUNT(*) FROM Employees) SET @NUMBER1=@NUMBER2
执行该存储过程，查看结果。
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 END
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 SET @BJ=0 ELSE
执行该存储过程，查看结果。
DECLARE @BJ int
EXEC COMPA '504209','302566',@BJ OUTPUT SELECT @BJ
3. 创建添加职员记录的存储过程EmployeeAdd。
CREATE PROCEDURE EmployeeADD ( ) AS BEGIN
INSERT INTO Employees
@workyear, @sex,@address,@phonenumber,@departmentID) VALUES(@employeeid,@name,@education,@birthday, END
@employeeid char(6),@name char(10),@education char(4),@birthday @workyear tinyint,@sex bit,@address char(40),@phonenumber char(12), @departmentID char(3) datetime,
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 GO
SET @em_cursor = CURSOR FORWARD_ONLY STATIC
FOR
SELECT * FROM Employees OPEN @em_cursor
END
声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录。
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 AS BEGIN END
IF(
(SELECT DepartmentID FROM inserted) NOT IN (SELECT DepartmentID FROM )
ROLLBACK
FOR INSERT,UPDATE
Departments)
插入或修改数据，查看效果。
验证之前需要删除之前的实验中建立的外键和约束关系，以确认触发器的有效性
2. 修改Departments表“DepartmentID”字段值时，该字段在Employees表中的对应值也做相应修改。
CREATE TRIGGER DepartmentUpdate ON Departments FOR UPDATE AS BEGIN
注意：需要先删除1.中建立的触发器，否则会报错。
UPDATE Employees
SET DepartmentID=(SELECT DepartmentID FROM inserted) WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
3. 删除Departments表中记录的同时删除该记录“DepartmentID”字段值在Employees表中
对应的记录。
CREATE TRIGGER DepartmentsDelete ON Departments FOR DELETE AS BEGIN GO
DELETE FROM Employees
WHERE DepartmentID=(SELECT DepartmentID FROM deleted)
END
4. 创建INSTEAD OF触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果存在则执行插入操作，如果不存在则提示“员工不存在”。
CREATE TRIGGER EM_EXISTS ON Salary
INSTEAD OF INSERT AS
DECLARE @EmployeeID char(6)
SET @EmployeeID=(SELECT EmployeeID FROM inserted) IF (@EmployeeID IN (SELECT EmployeeID FROM Employees))
INSERT INTO Salary SELECT * FROM inserted PRINT '员工号不存在' ELSE
BEGIN
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