记录生活
所有文章 分类 标签 60s Wallpaper Chat RSS Casual
记录生活

Contents

存储过程

 included in Other
   2284 words   5 minutes 
Beautiful landscape

Nature's Beauty

Explore the wonders of the world

参考资料

简介

存储过程是一段事先经过编译、存储在数据库中的 SQL 语句集合，可以像函数一样传入参数并调用执行。

优点

  • 可封装，隐藏复杂的处理逻辑
  • 可以传入参数，也可以返回值
  • 编写好的存储过程可重复使用
  • 安全性高，可设定用户对存储过程的使用权限

不足

不同的数据库系统，其存储过程可能存在差异，所以在迁移时可能需要重写部分内容。

下面记录的是 MySQL 的存储过程

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

DELIMITER //

CREATE PROCEDURE calculate_order_stats(
    IN customer_id INT,
    OUT total_amount DECIMAL(10,2),
    OUT order_count INT
)
BEGIN
    -- 声明局部变量（仅在存储过程内有效）
    DECLARE avg_amount DECIMAL(10,2);

    -- 计算总金额和订单数量
    SELECT SUM(amount), COUNT(*)
    INTO total_amount, order_count
    FROM orders
    WHERE customer_id = customer_id;

    -- 计算平均金额（演示局部变量使用）
    IF order_count > 0 THEN
        SET avg_amount = total_amount / order_count;

        -- 根据平均金额设置不同级别的日志
        IF avg_amount > 1000 THEN
            INSERT INTO customer_log(customer_id, message)
            VALUES (customer_id, CONCAT('VIP客户, 平均订单金额：', avg_amount));
        ELSE
            INSERT INTO customer_log(customer_id, message)
            VALUES (customer_id, CONCAT('普通客户, 平均订单金额：', avg_amount));
        END IF;
    ELSE
        SET total_amount = 0;
        SET order_count = 0;
        INSERT INTO customer_log(customer_id, message)
        VALUES (customer_id, '暂无订单');
    END IF;

END //

DELIMITER ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 准备测试数据
INSERT INTO orders VALUES (1, 101, 500.00);
INSERT INTO orders VALUES (2, 101, 1500.00);

-- 调用存储过程
SET @cust_id = 101;
SET @total = 0;
SET @count = 0;

CALL calculate_order_stats(@cust_id, @total, @count);

-- 查看结果
SELECT @total AS 总金额, @count AS 订单数;

-- 查看生成的日志
SELECT * FROM customer_log WHERE customer_id = 101;

关键语法说明

  • DELIMITER：用于更改命令结束符，以便在存储过程中使用 BEGIN ... END 语句。通常使用 // 作为新的结束符，定义结束后改回 ;
  • CREATE PROCEDURE：用于创建新的存储过程，后接存储过程名称
  • (IN 传入参数 类型, OUT 输出参数 类型)：定义输入和输出参数
  • BEGIN ... END：存储过程的主体，包含要执行的 SQL 语句

基础语法

用户变量

定义用户变量的方式：

1
2
3
4
5
6
7
8
9

-- 定义用户变量（会话级）
SET @user_email = 'john@example.com';
SET @discount_rate = 0.9;  -- 9折优惠

-- 或者在查询中设置
SELECT COUNT(*) INTO @total_users FROM users WHERE status = 'active';

-- 使用用户变量
SELECT @user_email AS email, @discount_rate AS discount, @total_users AS active_users;

用户变量使用 @ 符号，定义时可以做数值计算。存储过程中能直接读取 @x 的值并用于处理。注意滥用用户变量会导致程序难以理解和管理。

参数传递

  • IN（输入参数）：假设外部定义 SET @a = 1;，作为输入参数传入存储过程。存储过程中修改该参数 SET a = 233;，不影响外部 @a 的值

  • OUT（输出参数）：假设外部定义 SET @b = 1;，作为输出参数传入存储过程。存储过程读取不到该值（结果是 NULL），因为 OUT 参数专用于返回值。在存储过程中修改该参数会改变外部 @b 的值

  • INOUT（输入输出参数）：可以作为传入值，也可以作为存储过程的传出值

注意：确保参数名不等于列名，防止存储过程将参数当作列名处理。尽量少用 INOUT 参数。

流程控制

条件语句

if else：会员等级判定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

DELIMITER //

CREATE PROCEDURE check_member_level(IN user_id INT)
BEGIN
    DECLARE purchase_total DECIMAL(10,2);
    DECLARE member_level VARCHAR(20);

    -- 计算用户总消费
    SELECT COALESCE(SUM(amount), 0) INTO purchase_total
    FROM orders
    WHERE user_id = user_id AND status = 'completed';

    -- 根据消费金额确定会员等级
    IF purchase_total >= 10000 THEN
        SET member_level = '钻石会员';
        UPDATE users SET level = 'DIAMOND' WHERE id = user_id;
    ELSEIF purchase_total >= 5000 THEN
        SET member_level = '黄金会员';
        UPDATE users SET level = 'GOLD' WHERE id = user_id;
    ELSEIF purchase_total >= 1000 THEN
        SET member_level = '白银会员';
        UPDATE users SET level = 'SILVER' WHERE id = user_id;
    ELSE
        SET member_level = '普通会员';
        UPDATE users SET level = 'NORMAL' WHERE id = user_id;
    END IF;

    -- 返回结果
    SELECT
        user_id AS 用户ID,
        purchase_total AS 总消费额,
        member_level AS 会员等级;
END //

DELIMITER ;

CASE：订单状态处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

DELIMITER //

CREATE PROCEDURE handle_order_status(IN order_id INT)
BEGIN
    DECLARE current_status VARCHAR(20);
    DECLARE action_message VARCHAR(100);

    -- 获取当前订单状态
    SELECT status INTO current_status FROM orders WHERE id = order_id;

    -- 根据不同状态执行相应操作
    CASE current_status
        WHEN 'PENDING' THEN
            SET action_message = '发送支付提醒邮件';
            UPDATE orders SET reminder_sent = NOW() WHERE id = order_id;

        WHEN 'PAID' THEN
            SET action_message = '安排发货并通知仓库';
            UPDATE orders SET shipped_at = NOW() WHERE id = order_id;

        WHEN 'SHIPPED' THEN
            SET action_message = '发送物流跟踪信息';

        WHEN 'COMPLETED' THEN
            SET action_message = '请求用户评价';
            UPDATE orders SET review_requested = NOW() WHERE id = order_id;

        ELSE
            SET action_message = '未知状态，需要人工处理';
            INSERT INTO exception_log(order_id, issue) VALUES (order_id, '未知订单状态');
    END CASE;

    -- 记录操作日志
    INSERT INTO order_audit(order_id, action, executed_at)
    VALUES (order_id, action_message, NOW());

    SELECT order_id AS 订单号, action_message AS 执行操作;
END //

DELIMITER ;

循环语句

批量数据处理：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

DELIMITER //

CREATE PROCEDURE batch_update_prices(IN category_name VARCHAR(50), IN increase_rate DECIMAL(5,2))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE product_id INT;
    DECLARE old_price DECIMAL(10,2);
    DECLARE new_price DECIMAL(10,2);
    DECLARE update_count INT DEFAULT 0;

    -- 声明游标来处理产品列表
    DECLARE product_cursor CURSOR FOR
        SELECT id, price FROM products
        WHERE category = category_name AND status = 'active';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN product_cursor;

    read_loop: LOOP
        FETCH product_cursor INTO product_id, old_price;

        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 计算新价格
        SET new_price = old_price * (1 + increase_rate / 100);

        -- 更新产品价格
        UPDATE products SET price = new_price WHERE id = product_id;

        -- 记录价格变更历史
        INSERT INTO price_history(product_id, old_price, new_price, updated_at)
        VALUES (product_id, old_price, new_price, NOW());

        SET update_count = update_count + 1;
    END LOOP;

    CLOSE product_cursor;

    SELECT
        CONCAT('成功更新 ', update_count, ' 个产品的价格') AS 执行结果;
END //

DELIMITER ;

其他操作

  • 查询某个数据库中的所有存储过程
1
2

SHOW PROCEDURE STATUS
WHERE db = 'pt_dev'
  • 查看某个存储过程的详情
1

SHOW CREATE PROCEDURE pt_dev.fabricInspect_single_print_data
  • 删除存储过程
1

DROP PROCEDURE IF EXISTS operation_time_details();

与 SQL 函数的区别

函数（function）总是向调用者返回数据，一般只返回一个值。存储过程不直接返回数据，但可以通过改变输出参数的值来返回值，且输出参数的个数没有限制。

从实际应用来看：如果不需要返回值或需要多个返回值，使用存储过程；如果只需要一个返回值，使用函数。

function 定义中只能有 DDL（如 SELECT 等）语句；procedure 中主要是 DML 语句（对数据库进行复杂操作时，如 UPDATE、INSERT、QUERY、DELETE）。

拓展与后续学习建议

  • 触发器：学习在表上定义触发器，自动响应 INSERT、UPDATE、DELETE 事件
  • 事件调度器：了解如何利用 MySQL 事件调度器定期执行存储过程
  • 预处理语句：深入学习 PREPARE 和 EXECUTE 的使用
  • 错误处理：学习 DECLARE HANDLER 来处理存储过程中的异常情况
  • 性能考量：了解存储过程的优缺点，在何时应该使用或避免使用存储过程
Updated on 2024-04-12
 SQL, Stored Procedure
Back | Home