mysql while循环嵌套

DELIMITER //

CREATE PROCEDURE GenerateMultiplicationTable()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE result INT;

    WHILE i <= 9 DO
        WHILE j <= 9 DO
            SET result = i * j;
            SELECT CONCAT(i, ' * ', j, ' = ', result) AS multiplication;
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
        SET j = 1; -- 重置j以便开始下一行的计算
    END WHILE;
END //

DELIMITER ;

CALL GenerateMultiplicationTable();

-- 创建示例表
CREATE TABLE nested_data (
    id INT PRIMARY KEY,
    parent_id INT,
    data TEXT
);

-- 插入示例数据
INSERT INTO nested_data (id, parent_id, data) VALUES
(1, NULL, 'Root'),
(2, 1, 'Child 1'),
(3, 1, 'Child 2'),
(4, 2, 'Grandchild 1'),
(5, 2, 'Grandchild 2');

-- 创建临时表存储处理结果
CREATE TEMPORARY TABLE processed_data (
    id INT,
    path VARCHAR(255)
);

-- 使用临时表优化嵌套循环
DELIMITER //

CREATE PROCEDURE ProcessNestedData()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id INT;
    DECLARE current_parent_id INT;
    DECLARE current_path VARCHAR(255);

    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, parent_id FROM nested_data ORDER BY id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO current_id, current_parent_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 计算路径
        IF current_parent_id IS NULL THEN
            SET current_path = CONCAT(current_id);
        ELSE
            SELECT CONCAT(path, ' -> ', current_id) INTO current_path FROM processed_data WHERE id = current_parent_id;
        END IF;

        -- 插入处理结果
        INSERT INTO processed_data (id, path) VALUES (current_id, current_path);
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

CALL ProcessNestedData();

-- 查询处理结果
SELECT * FROM processed_data;