mysql存储过程声明数组

DELIMITER //

CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE array_size INT DEFAULT 5;
    CREATE TEMPORARY TABLE temp_array (id INT);
    
    -- 插入数据到临时表
    WHILE i < array_size DO
        INSERT INTO temp_array VALUES (i);
        SET i = i + 1;
    END WHILE;
    
    -- 查询并处理临时表数据
    SELECT * FROM temp_array;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_array;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE example_procedure(IN input_string VARCHAR(255))
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE array_element VARCHAR(255);
    DECLARE array_length INT DEFAULT LENGTH(input_string) - LENGTH(REPLACE(input_string, ',', '')) + 1;
    
    WHILE i <= array_length DO
        SET array_element = SUBSTRING_INDEX(SUBSTRING_INDEX(input_string, ',', i), ',', -1);
        -- 处理array_element
        SELECT array_element;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE json_array JSON;
    
    -- 设置JSON数组
    SET json_array = JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM your_table;
    
    -- 查询并处理JSON数组
    SELECT * FROM JSON_TABLE(json_array, '$[*]' COLUMNS (id INT PATH '$.id', name VARCHAR(255) PATH '$.name')) AS jt;
END //

DELIMITER ;