一、存储过程
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表中声明并打开一个游标。
CREATEsql触发器的使用 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。