mysql 存储过程两个结果集循环方式

原创

mob649e81586edc 2024-08-25 05:01:02 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e81586edc的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 存储过程中的多个结果集循环处理

在 MySQL 中，存储过程是一组预编译的 SQL 语句的集合，用于简化复杂的查询和数据操作。针对某些业务场景，我们可能需要处理多个结果集的情况。本文将探讨如何在 MySQL 存储过程中处理两个结果集，使用循环结构逐行遍历每个结果集，并以代码示例说明具体实现方法。

1. 理解存储过程

存储过程可以接收参数、执行 SQL 语句、返回结果等。一个存储过程的基本结构如下所示：

DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
    -- 过程体
END //
DELIMITER ;

2. 创建示例表和数据

为了演示如何创建两个结果集，我们首先创建两个表，并插入一些示例数据。假设我们有一个客户表 customers 和一个订单表 orders。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (name) VALUES 
('Alice'), 
('Bob'), 
('Charlie');

INSERT INTO orders (customer_id, amount) VALUES 
(1, 100.00), 
(1, 150.50), 
(2, 200.00);

3. 编写存储过程

我们将创建一个存储过程，该过程会查询两个结果集：一个是客户列表，另一个是订单列表。存储过程会通过循环逐行处理每个结果集。

DELIMITER //
CREATE PROCEDURE process_results()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cust_name VARCHAR(100);
    
    -- 定义游标
    DECLARE customer_cursor CURSOR FOR SELECT name FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 第一个结果集：客户列表
    OPEN customer_cursor;
    read_loop: LOOP
        FETCH customer_cursor INTO cust_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 打印客户名称
        SELECT CONCAT('Customer: ', cust_name) AS message;

        -- 第二个结果集：每个客户的订单
        SELECT amount FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = cust_name);
    END LOOP read_loop;

    CLOSE customer_cursor;
END //
DELIMITER ;

4. 调用存储过程

调用存储过程非常简单，只需使用如下 SQL 语句：

CALL process_results();

在执行 CALL process_results(); 之后，我们可以得到客户名称和对应的订单金额，两个结果集会按顺序返回，配合循环我们能很方便的逐行处理每个客户及其订单。

5. 数据可视化

为了更好地理解这两个结果集的关系，我们使用饼状图和关系图来进行可视化分析。

饼状图

以下是一个描述客户和订单的饼状图，表示不同客户的订单金额占比。

pie
    title 客户订单金额占比
    "Alice": 250.50
    "Bob": 200.00
    "Charlie": 0.00

关系图

接下来，我们绘制一个关系图，展示 customers 表和 orders 表之间的关系。

erDiagram
    CUSTOMERS {
        INT id PK "主键"
        STRING name "客户名称"
    }
    ORDERS {
        INT id PK "主键"
        INT customer_id FK "顾客ID"
        DECIMAL amount "订单金额"
    }
    CUSTOMERS ||--o{ ORDERS : "拥有"