用于迭代Json数组数据的MYSQL存储过程

CREATE PROCEDURE iterate_json_array()
BEGIN
    -- 存储过程逻辑
END;

CREATE PROCEDURE iterate_json_array()
BEGIN
    -- 解析Json数组
    SELECT *
    FROM JSON_TABLE('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]',
                    '$[*]'
                    COLUMNS (
                        name VARCHAR(255) PATH '$.name',
                        age INT PATH '$.age'
                    )
    ) AS jt;
END;

CREATE PROCEDURE iterate_json_array()
BEGIN
    -- 解析Json数组
    DECLARE done INT DEFAULT FALSE;
    DECLARE name VARCHAR(255);
    DECLARE age INT;
    DECLARE cur CURSOR FOR
        SELECT *
        FROM JSON_TABLE('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]',
                        '$[*]'
                        COLUMNS (
                            name VARCHAR(255) PATH '$.name',
                            age INT PATH '$.age'
                        )
        ) AS jt;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO name, age;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据逻辑
        -- 可以将数据插入到其他表格中，或进行其他操作
        
    END LOOP;
    CLOSE cur;
END;

CALL iterate_json_array();