文章详情 - 金星with休眠声音

加载中...

SQL

new

编程分享

MySQL 高级功能：存储过程、游标与触发器

发布日期：2025-10-16 08:06:25

第一章存储过程

1.1 存储过程概述

定义：为重复使用而保存的一条或多条 MySQL 语句的集合，可视为 “批处理文件”，但功能远超批处理。
背景：多数 SQL 语句是针对表的单条操作，而复杂业务需多条语句协同完成，存储过程可封装这些复杂操作。

1.2 为什么使用存储过程？

1.2.1 优点

简化复杂操作：将多步处理封装为单个可调用单元，降低使用难度。
保证数据完整性：所有开发人员 / 应用共用同一存储过程，避免代码不一致。减少手动执行多步操作的出错概率，确保数据一致性。
便于变动管理：若表名、列名或业务逻辑修改，仅需更新存储过程，调用者无需修改自身代码。
提升性能：执行存储过程比执行多条单条 SQL 更快。
支持特殊功能：可使用仅在单个请求中生效的 MySQL 元素，编写更灵活的代码。

核心好处总结：简单、安全、高性能。

1.2.2 缺点

编写难度高：存储过程语法比基础 SQL 复杂，需更高技能和经验。
权限限制：部分数据库管理员（DBA）限制存储过程的创建权限，仅允许使用不允许创建。

1.3 存储过程的基本操作

1.3.1 执行存储过程（调用）

MySQL 中执行存储过程的语句为 CALL，需指定存储过程名及参数（若有）。
示例：sql-- 调用 productpricing 存储过程，计算产品最低/最高/平均价格，结果存入变量 CALL productpricing(@pricelow, @pricehigh, @priceaverage);

1.3.2 创建存储过程

语法结构：sqlCREATE PROCEDURE 存储过程名(参数列表) BEGIN -- 存储过程体（SQL 语句集合） END;
注意：即使无参数，() 也必须保留；BEGIN 和 END 用于限定过程体范围。
示例 1：无参数存储过程（返回产品平均价格）sqlCREATE PROCEDURE productpricing() BEGIN SELECT AVG(prod_price) AS priceaverage FROM products; END;

1.3.3 MySQL 命令行的分隔符问题

问题：MySQL 默认分隔符为 ;，命令行工具会将存储过程体内的 ; 解析为语句结束，导致语法错误。
解决方法：临时修改分隔符，创建完成后恢复。sql-- 1. 将分隔符改为 // DELIMITER // -- 2. 创建存储过程 CREATE PROCEDURE productpricing() BEGIN SELECT AVG(prod_price) AS priceaverage FROM products; END // -- 3. 恢复分隔符为 ; DELIMITER ;
说明：除 \ 外，任何字符均可作为临时分隔符。

1.3.4 删除存储过程

语法：sql-- 直接删除（存储过程不存在时会报错） DROP PROCEDURE 存储过程名; -- 安全删除（存储过程不存在时不报错） DROP PROCEDURE IF EXISTS 存储过程名;
注意：删除时无需加 ()，仅需指定存储过程名。

1.4 存储过程的参数

1.4.1 参数类型

参数类型	作用	特点
`IN`	传入参数	调用时需传入值，存储过程内可使用
`OUT`	传出参数	存储过程内赋值，调用后可通过变量获取结果
`INOUT`	传入 + 传出	既需传入初始值，存储过程内可修改并传出

1.4.2 示例 1：带 OUT 参数的存储过程（计算产品价格极值与平均值）

-- 创建存储过程（3 个 OUT 参数分别存储最低、最高、平均价格）
CREATE PROCEDURE productpricing(
    OUT p1 DECIMAL(8,2),  -- 最低价格
    OUT ph DECIMAL(8,2),  -- 最高价格
    OUT pa DECIMAL(8,2)   -- 平均价格
)
BEGIN
    SELECT MIN(prod_price) INTO p1 FROM products;
    SELECT MAX(prod_price) INTO ph FROM products;
    SELECT AVG(prod_price) INTO pa FROM products;
END;

-- 调用存储过程（需传入 3 个用户变量）
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

-- 查看结果（通过变量获取传出值）
SELECT @pricelow, @pricehigh, @priceaverage;

注意：MySQL 变量必须以 @ 开头。

1.4.3 示例 2：带 IN + OUT 参数的存储过程（计算订单合计）

-- 创建存储过程（IN 传入订单号，OUT 传出订单合计）
CREATE PROCEDURE ordertotal(
    IN onumber INT,       -- 订单号（传入）
    OUT ototal DECIMAL(8,2) -- 订单合计（传出）
)
BEGIN
    SELECT SUM(item_price * quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;  -- 计算结果存入 OUT 参数
END;

-- 调用（计算订单号 2005 的合计）
CALL ordertotal(2005, @total);

-- 查看结果
SELECT @total;

1.5 智能存储过程（含业务逻辑）

1.5.1 示例：带条件判断的订单合计（含营业税）

-- 创建存储过程（支持是否加营业税的条件判断）
CREATE PROCEDURE ordertotal(
    IN onumber INT,        -- 订单号（IN）
    IN taxable BOOLEAN,    -- 是否加税（1=加，0=不加）（IN）
    OUT ototal DECIMAL(8,2) -- 最终合计（OUT）
) 
COMMENT 'Obtain order total, optionally adding tax' -- 备注（SHOW PROCEDURE 可查看）
BEGIN
    -- 1. 定义局部变量（仅存储过程内有效）
    DECLARE total DECIMAL(8,2);       -- 基础合计
    DECLARE taxrate INT DEFAULT 6;    -- 营业税率（默认 6%）

    -- 2. 计算基础合计（不含税）
    SELECT SUM(item_price * quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    -- 3. 条件判断：是否加税
    IF taxable THEN
        SELECT total + (total / 100 * taxrate) INTO total;
    END IF;

    -- 4. 赋值给 OUT 参数
    SELECT total INTO ototal;
END;

-- 调用（订单 2005，加税）
CALL ordertotal(2005, 1, @total);
SELECT @total;

关键特性：COMMENT：添加备注，便于维护。DECLARE：定义局部变量（需指定类型，可设默认值）。IF 语句：实现业务逻辑判断（支持 ELSEIF、ELSE）。

1.6 查看存储过程信息

查看创建语句：sqlSHOW CREATE PROCEDURE ordertotal;
查看详细信息（创建时间、作者等）：sql-- 查看所有存储过程 SHOW PROCEDURE STATUS; -- 过滤特定存储过程（如 ordertotal） SHOW PROCEDURE STATUS LIKE 'ordertotal';

第二章游标

2.1 游标概述

定义：存储在 MySQL 服务器上的 “结果集指针”，对应一条 SELECT 语句的结果，支持逐行读取数据（无法直接批量处理）。
适用场景：交互式应用中需滚动浏览数据（如 “下一行”“上一行”），或逐行处理结果集（如批量计算、数据迁移）。
限制：仅能在存储过程中使用。

2.2 游标使用步骤

声明 → 打开 → 读取 → 关闭

声明游标：定义游标关联的 SELECT 语句（不实际检索数据）。
打开游标：执行关联的 SELECT 语句，加载结果集到游标。
读取数据：逐行获取游标中的数据（通过 FETCH）。
关闭游标：释放游标占用的内存和资源（未手动关闭则在存储过程结束时自动关闭）。

2.3 游标操作示例

2.3.1 基础示例：逐行读取订单号

CREATE PROCEDURE processorders()
BEGIN
    -- 1. 声明局部变量
    DECLARE done BOOLEAN DEFAULT 0;  -- 循环终止标记（0=未结束，1=结束）
    DECLARE o INT;                   -- 存储订单号

    -- 2. 声明游标（关联“查询所有订单号”的 SELECT 语句）
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- 3. 声明“游标结束”处理器（当无更多行时，设置 done=1）
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- 4. 打开游标（执行 SELECT 语句，加载结果集）
    OPEN ordernumbers;

    -- 5. 循环读取数据（REPEAT 循环：先执行，后判断）
    REPEAT
        -- 读取当前行的订单号，存入变量 o
        FETCH ordernumbers INTO o;
    UNTIL done END REPEAT;  -- 当 done=1 时终止循环

    -- 6. 关闭游标
    CLOSE ordernumbers;
END;

关键说明：SQLSTATE '02000'：MySQL 内置状态码，表示 “无更多行”（游标到达末尾）。DECLARE 次序：局部变量 → 游标 → 处理器（违反会报错）。

2.3.2 实用示例：批量计算订单合计并插入新表

CREATE PROCEDURE processorders()
BEGIN
    -- 1. 声明局部变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;         -- 订单号
    DECLARE t DECIMAL(8,2);-- 订单合计（含税）

    -- 2. 声明游标（查询所有订单号）
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- 3. 声明处理器（游标结束时设 done=1）
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- 4. 创建临时表（存储订单合计结果，不存在则创建）
    CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT,
        total DECIMAL(8,2)
    );

    -- 5. 打开游标
    OPEN ordernumbers;

    -- 6. 循环读取并处理数据
    REPEAT
        -- 读取订单号
        FETCH ordernumbers INTO o;
        -- 调用之前的 ordertotal 存储过程，计算含税合计
        CALL ordertotal(o, 1, t);
        -- 将结果插入 ordertotals 表
        INSERT INTO ordertotals(order_num, total) VALUES(o, t);
    UNTIL done END REPEAT;

    -- 7. 关闭游标
    CLOSE ordernumbers;
END;

-- 调用存储过程后，查看结果
SELECT * FROM ordertotals;

第三章触发器

3.1 触发器概述

定义：MySQL 响应 DELETE/INSERT/UPDATE 语句时自动执行的 SQL 语句（或语句块）。
适用场景：数据验证（如插入顾客时检查手机号格式）。数据联动（如订购产品时自动减少库存）。审计跟踪（如删除数据时自动存档）。
限制：仅支持表，不支持视图；MySQL 5 及以上版本支持。

3.2 触发器的核心要素

唯一触发器名：同一数据库内触发器名唯一。
关联表：触发器作用于哪个表。
触发事件：DELETE/INSERT/UPDATE（仅这 3 种）。
触发时机：BEFORE（事件执行前）/AFTER（事件执行后）。

3.3 触发器的基本操作

3.3.1 创建触发器

语法：sqlCREATE TRIGGER 触发器名触发时机触发事件 ON 关联表 FOR EACH ROW -- 触发器体（单条语句可直接写，多条需用 BEGIN...END 包裹）触发器体;
说明：FOR EACH ROW 表示对每一行数据的触发事件都执行触发器体（避免批量操作时遗漏）。
示例 1：INSERT 触发器（插入产品后提示）sql-- 触发器名：newproduct；触发时机：INSERT 后；关联表：products CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' AS message;

3.3.2 删除触发器

语法：sqlDROP TRIGGER 触发器名;
注意：触发器无法直接修改，需先删除再重新创建。

3.4 不同类型触发器的使用

3.4.1 INSERT 触发器

关键特性：可通过 NEW 虚拟表访问插入的行数据。BEFORE INSERT 中可修改 NEW 的值（如净化数据）。AUTO_INCREMENT 列在 BEFORE INSERT 时 NEW 值为 0，AFTER INSERT 时为新生成的自增值。
示例：插入顾客时自动转换邮箱为小写sqlCREATE TRIGGER clean_cust_email BEFORE INSERT ON customers FOR EACH ROW SET NEW.cust_email = LOWER(NEW.cust_email);

3.4.2 DELETE 触发器

关键特性：可通过 OLD 虚拟表访问被删除的行数据。OLD 中的值只读，无法修改。
示例：删除订单前自动存档sql-- 1. 先创建存档表（与 orders 表结构一致） CREATE TABLE IF NOT EXISTS archive_orders( order_num INT, order_date DATETIME, cust_id INT ); -- 2. 创建 DELETE 触发器（删除 orders 前存档） CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES(OLD.order_num, OLD.order_date, OLD.cust_id); END;
优势：BEFORE DELETE 若存档失败，订单删除操作会自动放弃，保证数据完整性。

3.4.3 UPDATE 触发器

关键特性：可通过 OLD 访问修改前的数据，NEW 访问修改后的数据。BEFORE UPDATE 中可修改 NEW 的值（如强制数据格式）。OLD 只读，NEW 可修改（BEFORE 时）。
示例：更新供应商时强制州名缩写为大写sqlCREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);

3.5 触发器的注意事项

触发器数量限制：每个表针对每个触发事件（DELETE/INSERT/UPDATE）的每个时机（BEFORE/AFTER）仅能创建 1 个触发器，即每个表最多 6 个触发器。
触发器失败影响：BEFORE 触发器失败 → 后续触发事件（如 INSERT）不执行。

文章目录

SQL

new

编程分享

第一章存储过程

1.1 存储过程概述

1.2 为什么使用存储过程？

1.2.1 优点

1.2.2 缺点

1.3 存储过程的基本操作

1.3.1 执行存储过程（调用）

1.3.2 创建存储过程

1.3.3 MySQL 命令行的分隔符问题

1.3.4 删除存储过程

1.4 存储过程的参数

1.4.1 参数类型

1.4.2 示例 1：带 OUT 参数的存储过程（计算产品价格极值与平均值）

1.4.3 示例 2：带 IN + OUT 参数的存储过程（计算订单合计）

1.5 智能存储过程（含业务逻辑）

1.5.1 示例：带条件判断的订单合计（含营业税）

1.6 查看存储过程信息

第二章游标

2.1 游标概述

2.2 游标使用步骤

2.3 游标操作示例

2.3.1 基础示例：逐行读取订单号

2.3.2 实用示例：批量计算订单合计并插入新表

第三章触发器

3.1 触发器概述

3.2 触发器的核心要素

3.3 触发器的基本操作

3.3.1 创建触发器

3.3.2 删除触发器

3.4 不同类型触发器的使用

3.4.1 INSERT 触发器

3.4.2 DELETE 触发器

3.4.3 UPDATE 触发器

3.5 触发器的注意事项

渝ICP备2025057367号-1 since 2025 © 大金星的个人网址

文章目录

SQL

new

编程分享

第一章 存储过程

1.1 存储过程概述

1.2 为什么使用存储过程？

1.2.1 优点

1.2.2 缺点

1.3 存储过程的基本操作

1.3.1 执行存储过程（调用）

1.3.2 创建存储过程

1.3.3 MySQL 命令行的分隔符问题

1.3.4 删除存储过程

1.4 存储过程的参数

1.4.1 参数类型

1.4.2 示例 1：带 OUT 参数的存储过程（计算产品价格极值与平均值）

1.4.3 示例 2：带 IN + OUT 参数的存储过程（计算订单合计）

1.5 智能存储过程（含业务逻辑）

1.5.1 示例：带条件判断的订单合计（含营业税）

1.6 查看存储过程信息

第二章 游标

2.1 游标概述

2.2 游标使用步骤

2.3 游标操作示例

2.3.1 基础示例：逐行读取订单号

2.3.2 实用示例：批量计算订单合计并插入新表

第三章 触发器

3.1 触发器概述

3.2 触发器的核心要素

3.3 触发器的基本操作

3.3.1 创建触发器

3.3.2 删除触发器

3.4 不同类型触发器的使用

3.4.1 INSERT 触发器

3.4.2 DELETE 触发器

3.4.3 UPDATE 触发器

3.5 触发器的注意事项

渝ICP备2025057367号-1 since 2025 © 大金星的个人网址

第一章存储过程

第二章游标

第三章触发器