MySQL存储过程之：`存储过程`中的`循环`控制：`LOOP`、`WHILE`和`REPEAT`的用法。

好的，我们开始今天的讲座：MySQL存储过程中的循环控制。循环是编程中不可或缺的一部分，它允许我们重复执行一段代码块，直到满足特定条件为止。在MySQL存储过程中，我们有三种主要的循环结构：LOOP、WHILE和REPEAT。理解并熟练掌握这三种循环的用法，对于编写高效、灵活的存储过程至关重要。

一、LOOP循环

LOOP循环是最基本的循环结构，它会无限循环执行代码块，除非使用LEAVE语句显式退出循环。

1.1 基本语法

[loop_label:] LOOP
    statement_list
END LOOP [loop_label];

• loop_label：可选的循环标签，用于在循环内部使用LEAVE语句退出指定的循环。
• statement_list：循环体，包含需要重复执行的SQL语句。

1.2 示例：无限循环与LEAVE语句

DROP PROCEDURE IF EXISTS loop_example;
CREATE PROCEDURE loop_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    my_loop: LOOP
        SET counter = counter + 1;
        SELECT counter;
        IF counter >= 10 THEN
            LEAVE my_loop; -- 退出名为my_loop的循环
        END IF;
    END LOOP my_loop;
    SELECT 'Loop finished';
END;

CALL loop_example();

在这个例子中，my_loop是一个标签，用于标识LOOP循环。LEAVE my_loop语句用于退出这个循环。如果省略LEAVE语句，循环将无限执行下去。

1.3 示例：使用ITERATE语句跳过当前迭代

ITERATE语句可以跳过当前循环迭代，直接进入下一次迭代。

DROP PROCEDURE IF EXISTS loop_iterate_example;
CREATE PROCEDURE loop_iterate_example()
BEGIN
    DECLARE counter INT DEFAULT 0;
    my_loop: LOOP
        SET counter = counter + 1;
        IF counter MOD 2 = 0 THEN
            ITERATE my_loop; -- 跳过偶数的迭代
        END IF;
        SELECT counter;
        IF counter >= 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    SELECT 'Loop finished';
END;

CALL loop_iterate_example();

在这个例子中，当counter是偶数时，ITERATE my_loop语句会跳过SELECT counter语句，直接进入下一次循环迭代。因此，只有奇数会被打印出来。

二、WHILE循环

WHILE循环在满足指定条件的情况下，重复执行代码块。

2.1 基本语法

[while_label:] WHILE condition DO
    statement_list
END WHILE [while_label];

• while_label：可选的循环标签。
• condition：循环条件，当条件为真（TRUE）时，循环继续执行。
• statement_list：循环体。

2.2 示例：计算1到10的和

DROP PROCEDURE IF EXISTS while_example;
CREATE PROCEDURE while_example()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    WHILE counter <= 10 DO
        SET sum = sum + counter;
        SET counter = counter + 1;
    END WHILE;

    SELECT sum;
END;

CALL while_example();

在这个例子中，WHILE循环会一直执行，直到counter大于10为止。循环体内部计算了1到10的和，并存储在sum变量中。

2.3 示例：使用WHILE循环更新数据

假设我们有一个名为products的表，其中包含id和price两列。我们想将所有价格低于10的产品价格提高到10。

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(10, 2)
);

INSERT INTO products (price) VALUES (5.00), (8.00), (12.00), (3.00), (15.00);

DROP PROCEDURE IF EXISTS update_prices;
CREATE PROCEDURE update_prices()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE product_id INT;

    DECLARE cur CURSOR FOR SELECT id FROM products WHERE price < 10;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO product_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE products SET price = 10 WHERE id = product_id;
    END LOOP;

    CLOSE cur;
END;

CALL update_prices();

SELECT * FROM products;

这个例子使用了游标（CURSOR）和WHILE循环来遍历products表中价格低于10的产品，并将它们的价格更新为10。 虽然这里使用了LOOP，但是整体的逻辑结构更贴近WHILE循环，因为done变量控制了循环的退出。

三、REPEAT循环

REPEAT循环与WHILE循环类似，但是REPEAT循环会先执行一次循环体，然后再检查循环条件。也就是说，REPEAT循环至少会执行一次。

3.1 基本语法

[repeat_label:] REPEAT
    statement_list
UNTIL condition
END REPEAT [repeat_label];

• repeat_label：可选的循环标签。
• statement_list：循环体。
• condition：循环条件，当条件为真（TRUE）时，循环结束。

3.2 示例：计算1到10的和

DROP PROCEDURE IF EXISTS repeat_example;
CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    REPEAT
        SET sum = sum + counter;
        SET counter = counter + 1;
    UNTIL counter > 10
    END REPEAT;

    SELECT sum;
END;

CALL repeat_example();

这个例子与WHILE循环的例子功能相同，都是计算1到10的和。不同之处在于，REPEAT循环会先执行一次循环体，然后再检查counter > 10这个条件。

3.3 示例：确保插入唯一值

假设我们有一个表unique_values，其中有一列value需要保证唯一性。我们可以使用REPEAT循环来尝试插入一个随机值，直到插入成功为止。

DROP TABLE IF EXISTS unique_values;
CREATE TABLE unique_values (
    value INT UNIQUE
);

DROP PROCEDURE IF EXISTS insert_unique_value;
CREATE PROCEDURE insert_unique_value()
BEGIN
    DECLARE new_value INT;
    DECLARE duplicate_key BOOLEAN DEFAULT FALSE;

    REPEAT
        SET new_value = FLOOR(RAND() * 100); -- 生成0到99之间的随机整数
        SET duplicate_key = FALSE;

        BEGIN
            DECLARE EXIT HANDLER FOR 1062 -- 1062是重复键的错误代码
            BEGIN
                SET duplicate_key = TRUE;
            END;

            INSERT INTO unique_values (value) VALUES (new_value);
        END;

    UNTIL NOT duplicate_key
    END REPEAT;

    SELECT new_value;
END;

CALL insert_unique_value();
SELECT * FROM unique_values;

在这个例子中，我们使用了一个异常处理程序（HANDLER）来捕获重复键的错误（错误代码1062）。如果插入操作因为重复键而失败，duplicate_key变量会被设置为TRUE，循环会继续执行，直到找到一个唯一的随机值并成功插入为止。

四、三种循环的比较

特性	LOOP	WHILE	REPEAT
执行次数	至少执行零次，通常配合 LEAVE 使用	在条件为真时执行，可能零次	至少执行一次
循环条件检查	无内置条件检查，需要显式使用 IF 和 LEAVE	在循环开始前检查条件	在循环结束后检查条件
适用场景	需要无限循环，并在特定条件下退出的场景	在循环开始前就需要判断是否执行的场景	至少需要执行一次循环体的场景
退出方式	使用 LEAVE 语句显式退出	条件变为假（FALSE）时退出	条件变为真（TRUE）时退出

五、循环的嵌套

循环可以嵌套使用，即在一个循环体内部包含另一个循环。嵌套循环可以用于处理更复杂的问题。

5.1 示例：生成乘法表

DROP PROCEDURE IF EXISTS multiplication_table;
CREATE PROCEDURE multiplication_table()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT;

    outer_loop: WHILE i <= 9 DO
        SET j = 1;
        inner_loop: WHILE j <= i DO
            SELECT CONCAT(j, '*', i, '=', j * i);
            SET j = j + 1;
        END WHILE inner_loop;
        SET i = i + 1;
    END WHILE outer_loop;
END;

CALL multiplication_table();

在这个例子中，外层WHILE循环控制行数，内层WHILE循环控制列数。通过嵌套循环，我们可以生成一个完整的乘法表。

六、注意事项

• 避免无限循环： 在编写循环时，务必确保循环最终能够退出。否则，存储过程可能会无限执行下去，导致数据库性能下降甚至崩溃。
• 优化循环体： 循环体内部的代码会被重复执行多次，因此应该尽量优化循环体内部的代码，避免执行不必要的计算或查询。
• 使用游标时注意关闭： 如果在循环中使用了游标，务必在循环结束后关闭游标，释放资源。
• 考虑使用集合操作代替循环： 在某些情况下，可以使用集合操作（例如UPDATE ... SELECT）来代替循环，提高性能。

七、最佳实践

• 选择合适的循环类型： 根据实际需求选择最合适的循环类型。例如，如果需要至少执行一次循环体，可以使用REPEAT循环。如果需要在循环开始前判断是否执行，可以使用WHILE循环。如果需要无限循环，并在特定条件下退出，可以使用LOOP循环。
• 使用标签： 为循环添加标签可以提高代码的可读性，并方便使用LEAVE和ITERATE语句退出或跳过指定的循环。
• 注释代码： 在代码中添加注释可以帮助其他人理解代码的逻辑，并方便日后维护。

八、案例分析

假设我们需要编写一个存储过程，用于计算每个用户的订单总金额，并将结果存储在一个新的表中。

1. 创建表结构：

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2)
);

DROP TABLE IF EXISTS user_order_totals;
CREATE TABLE user_order_totals (
    user_id INT PRIMARY KEY,
    total_amount DECIMAL(10, 2)
);

INSERT INTO orders (user_id, amount) VALUES
(1, 100.00), (1, 50.00), (2, 75.00), (2, 25.00), (3, 200.00);

2. 编写存储过程：

DROP PROCEDURE IF EXISTS calculate_user_order_totals;
CREATE PROCEDURE calculate_user_order_totals()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE current_user_id INT;
    DECLARE total_amount DECIMAL(10, 2);

    -- 声明游标
    DECLARE user_cursor CURSOR FOR
        SELECT DISTINCT user_id FROM orders;

    -- 声明 NOT FOUND 处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN user_cursor;

    read_loop: LOOP
        FETCH user_cursor INTO current_user_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 计算当前用户的订单总金额
        SELECT SUM(amount) INTO total_amount FROM orders WHERE user_id = current_user_id;

        -- 插入或更新 user_order_totals 表
        INSERT INTO user_order_totals (user_id, total_amount)
        VALUES (current_user_id, total_amount)
        ON DUPLICATE KEY UPDATE total_amount = total_amount;

    END LOOP;

    CLOSE user_cursor;
END;

CALL calculate_user_order_totals();

SELECT * FROM user_order_totals;

这个存储过程使用游标遍历orders表中所有不同的user_id，然后计算每个用户的订单总金额，并将结果存储在user_order_totals表中。如果user_order_totals表中已经存在该用户的记录，则更新总金额；否则，插入一条新的记录。

掌握循环控制对于编写复杂的存储过程是必不可少的。选择正确的循环类型，避免无限循环，并优化循环体内部的代码，可以帮助我们编写出高效、可靠的存储过程。

记住关键点

• LOOP无条件循环，需要LEAVE退出。
• WHILE在条件为真时循环，可能不执行。
• REPEAT至少执行一次，直到条件为真。