03 数据库编程专项：存储过程、触发器、游标、嵌入式 SQL

数据库编程题是本课程大题高频点。new-pic 和历年题都出现过：带参数存储过程、输出参数、游标、触发器、嵌入式 SQL 填空、事务错误处理。

一、存储过程总思路

存储过程是一组预先编译保存在数据库中的 SQL 语句和流程控制语句。它适合完成经常调用、逻辑较复杂、需要参数的数据库操作。

答题结构：

如有要求，先删除同名过程。
CREATE PROCEDURE 声明过程名和参数。
声明局部变量。
写核心 SQL。
按题意处理不存在、NULL、库存不足等情况。
必要时写调用验证。

二、不带参数与带参数存储过程

不带参数

CREATE PROCEDURE ListAllStudents
AS
BEGIN
    SELECT Sno, Sname, Sdept
    FROM Student;
END;
GO

EXEC ListAllStudents;

带输入参数

CREATE PROCEDURE ListStudentsByDept
    @dept varchar(20)
AS
BEGIN
    SELECT Sno, Sname, Sdept
    FROM Student
    WHERE Sdept = @dept;
END;
GO

EXEC ListStudentsByDept @dept = N'计算机科学与技术';

带输出参数

题型：Employees(emp_id, emp_name, dept_id, salary)，输入员工号，输出员工姓名；若不存在或输入为 NULL，输出“无此员工”。

SQL Server 写法：

CREATE PROCEDURE proc_get_employee_name
    @in_emp_id int,
    @out_emp_name varchar(50) OUTPUT
AS
BEGIN
    IF @in_emp_id IS NULL
    BEGIN
        SET @out_emp_name = N'无此员工';
        RETURN;
    END;

    SELECT @out_emp_name = emp_name
    FROM Employees
    WHERE emp_id = @in_emp_id;

    IF @out_emp_name IS NULL
        SET @out_emp_name = N'无此员工';
END;
GO

DECLARE @name varchar(50);
EXEC proc_get_employee_name 1001, @name OUTPUT;
SELECT @name AS 员工姓名;

MySQL/通用过程题若明确写 IN、OUT，可写：

CREATE PROCEDURE proc_get_employee_name(
    IN in_emp_id INT,
    OUT out_emp_name VARCHAR(50)
)
BEGIN
    IF in_emp_id IS NULL THEN
        SET out_emp_name = '无此员工';
    ELSE
        SELECT emp_name
        INTO out_emp_name
        FROM Employees
        WHERE emp_id = in_emp_id;

        IF out_emp_name IS NULL THEN
            SET out_emp_name = '无此员工';
        END IF;
    END IF;
END;

考试若没有指定 DBMS，本课程课堂多按 SQL Server/T-SQL 写。

三、库存出库/入库存储过程

出库题模板

题型：BOOK(BID, BNAME, QTY)，出库时若库存足够则扣减并通知余量，否则提示库存不足。参数：书号 @bid、出库数量 @s。

CREATE PROCEDURE BookOut
    @bid char(10),
    @s int
AS
BEGIN
    DECLARE @qty int;

    SELECT @qty = QTY
    FROM BOOK
    WHERE BID = @bid;

    IF @qty IS NULL
    BEGIN
        PRINT N'无此图书';
        RETURN;
    END;

    IF @qty >= @s
    BEGIN
        UPDATE BOOK
        SET QTY = QTY - @s
        WHERE BID = @bid;

        SELECT BID, BNAME, QTY AS 库存余量
        FROM BOOK
        WHERE BID = @bid;
    END
    ELSE
    BEGIN
        PRINT N'库存不足';
    END
END;
GO

EXEC BookOut @bid = 'B001', @s = 10;

入库题模板

若存在则增加库存；不存在则插入新书，书名暂为空，并提示“新书入库”。

CREATE PROCEDURE BookIn
    @bid char(10),
    @s int
AS
BEGIN
    IF EXISTS (SELECT 1 FROM BOOK WHERE BID = @bid)
    BEGIN
        UPDATE BOOK
        SET QTY = QTY + @s
        WHERE BID = @bid;

        SELECT BID, BNAME, QTY AS 最新库存
        FROM BOOK
        WHERE BID = @bid;
    END
    ELSE
    BEGIN
        INSERT INTO BOOK(BID, BNAME, QTY)
        VALUES (@bid, NULL, @s);
        PRINT N'新书入库';
    END
END;
GO

四、游标

游标用于协调 SQL 的集合处理方式与宿主语言/过程语言的单记录处理方式。选择填空常问：游标是用于逐行处理查询结果集的机制。

SQL Server 游标步骤：

定义游标。
打开游标。
读取/推进游标。
循环处理。
关闭游标。
释放游标。

模板：

DECLARE Cur1 CURSOR
FOR
    SELECT p.pno, p.pname, d.dname, d.mana
    FROM Pe AS p
    JOIN Dept AS d ON p.dptno = d.dptno
    WHERE p.pname LIKE N'_武%';

OPEN Cur1;

DECLARE @pno varchar(10),
        @pname varchar(20),
        @dname varchar(20),
        @mana varchar(20);

FETCH NEXT FROM Cur1
INTO @pno, @pname, @dname, @mana;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT 职工号 = @pno,
           姓名 = @pname,
           部门 = @dname,
           负责人 = @mana;

    FETCH NEXT FROM Cur1
    INTO @pno, @pname, @dname, @mana;
END;

CLOSE Cur1;
DEALLOCATE Cur1;

注意：

FETCH NEXT FROM 游标 INTO 变量列表 的变量数量和查询列数量必须一致。
循环条件是 @@FETCH_STATUS = 0。
关闭后还要 DEALLOCATE 释放。

五、带参数存储过程加游标

题型：查询姓名第二个字为“武”的员工的部门名和负责人，并以标签格式输出。

IF EXISTS (
    SELECT name
    FROM sysobjects
    WHERE name = 'Sele_staff'
      AND type = 'P'
)
DROP PROCEDURE Sele_staff;
GO

CREATE PROCEDURE Sele_staff
    @second_char nchar(1)
AS
BEGIN
    DECLARE Cur1 CURSOR
    FOR
        SELECT p.pno, p.pname, d.dname, d.mana
        FROM Pe AS p
        JOIN Dept AS d ON p.dptno = d.dptno
        WHERE p.pname LIKE N'_' + @second_char + N'%';

    OPEN Cur1;

    DECLARE @pno varchar(10),
            @pname nvarchar(20),
            @dname nvarchar(20),
            @mana nvarchar(20);

    FETCH NEXT FROM Cur1 INTO @pno, @pname, @dname, @mana;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT 职工号 = @pno,
               姓名 = @pname,
               部门 = @dname,
               负责人 = @mana;

        FETCH NEXT FROM Cur1 INTO @pno, @pname, @dname, @mana;
    END;

    CLOSE Cur1;
    DEALLOCATE Cur1;
END;
GO

EXEC Sele_staff N'武';

六、触发器

触发器是一种特殊存储过程，由事件自动触发，不能通过普通 EXEC 调用。它常用于复杂完整性约束、审计、联动维护。

触发器通常“不带用户手工传入的参数”。它不是像存储过程那样 EXEC proc @x=... 调用，而是在表发生 INSERT/UPDATE/DELETE 时由 DBMS 自动执行。触发器要获取本次被修改的数据，主要靠 inserted 和 deleted 这两张逻辑表。

常见触发时机：

AFTER INSERT
AFTER UPDATE
AFTER DELETE
INSTEAD OF INSERT/UPDATE/DELETE

SQL Server 触发器中常用临时表：

inserted：插入后的新行；更新时也保存更新后的新行。
deleted：删除前的旧行；更新时也保存更新前的旧行。

营业时间触发器

题型：SaleDetail 只能在工作日 9:00-17:00 修改。

CREATE TRIGGER trg_SaleDetail_business_hours
ON SaleDetail
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @hour int, @weekday int;

    SELECT @hour = DATEPART(hh, GETDATE());
    SELECT @weekday = DATEPART(weekday, GETDATE());

    IF NOT (@hour BETWEEN 9 AND 17 AND @weekday IN (2, 3, 4, 5, 6))
    BEGIN
        PRINT N'Not business hours!';
        ROLLBACK;
    END;
END;
GO

DATEPART(weekday, GETDATE()) 的结果受 DATEFIRST 影响。课堂题一般默认周一到周五为 2-6。若想更稳，可先 SET DATEFIRST 7，但考场通常不用展开。

七、事务与错误处理

多步删除/修改必须使用事务，保证全部成功才提交，任何一步失败要回滚。

例：删除学生时先删除成绩，再删除学生。

CREATE PROCEDURE DeleteStudent
    @sno char(9)
AS
BEGIN
    BEGIN TRANSACTION;

    DELETE FROM Grade WHERE Sno = @sno;
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT N'删除成绩失败，事务已回滚';
        RETURN;
    END;

    DELETE FROM Student WHERE Sno = @sno;
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT N'删除学生失败，事务已回滚';
        RETURN;
    END;

    COMMIT TRANSACTION;
    PRINT N'学生删除成功，事务已提交';
END;
GO

@@ERROR 的规则：

每条语句执行后必须立刻判断。
不能等多条语句执行完再判断，因为会被后续语句覆盖。
出错必须回滚。
出错必须退出过程，避免继续执行破坏一致性。

八、嵌入式 SQL

嵌入式 SQL 是把 SQL 语句嵌入 C 等宿主语言中使用。它解决的问题是：SQL 一次处理一组记录，宿主语言通常一次处理一条记录，两者通过主变量、SQLCA、游标协调。

核心术语：

主变量：宿主语言变量，SQL 中使用时前面加冒号，如 :deptname。
SQLCA：SQL 通信区，保存 SQL 执行状态，如 sqlca.sqlcode。
游标：逐行处理查询结果集。

典型填空：

EXEC SQL INCLUDE SQLCA;        /* 定义 SQL 通信区 */

EXEC SQL DECLARE SX CURSOR FOR
SELECT Sno, Sname, Ssex, Sage
FROM Student
WHERE Sdept = :deptname;

EXEC SQL OPEN SX;

EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;

EXEC SQL UPDATE Student
SET Sage = :NEWAGE
WHERE CURRENT OF SX;

完整流程：

EXEC SQL BEGIN DECLARE SECTION; 和 END DECLARE SECTION; 声明主变量。
EXEC SQL INCLUDE SQLCA; 引入通信区。
EXEC SQL CONNECT ... 连接数据库。
DECLARE cursor CURSOR FOR SELECT ... 定义游标。
OPEN cursor 打开游标。
循环 FETCH cursor INTO :变量。
判断 sqlca.sqlcode != 0 退出。
如需更新当前行，用 WHERE CURRENT OF cursor。
CLOSE cursor，COMMIT WORK，DISCONNECT。