MySQL:传递给具有多列和行的PROCEDURE查询结果

Question

类似的话题有很多，但没有人提供答案。

问题:

1. 如何将多列多行的查询结果传递给存储过程？
2. 如何在存储过程中迭代该结果的每一行？

示例:

我有:

SELECT field1, field2 FROM [not essential from where]; -- returns 100 rows

我想要:类似的东西

DELIMITER ////
CREATE PROCEDURE test(IN query_result VARCHAR(32))
BEGIN
    -- Iterate through each row of query result and
    -- SELECT CONCAT('Row with field1 ', field1, ' and field2 ', field2, ' was selected');
END ////
DELIMITER ; 

CALL test((SELECT field1, field2 FROM [not essential from where]));

Answer 1

MySQL不支持在存储过程中传递查询结果作为参数，但您可以尝试传递sql查询并将其用作游标:

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_test$$

CREATE PROCEDURE `sp_test`(p_query varchar(250))
BEGIN
    -- Declarations
    declare var1 varchar(250); -- change as need
    declare var2 varchar(250); -- change as need
    declare no_more_rows bool default false;

    DECLARE cursor1 CURSOR FOR select * from tmp_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;


    -- creating temporary table and open as cursor
    drop temporary table if exists tmp_table;
    SET @prepared_sql = concat('create temporary table tmp_table ',p_query);
    PREPARE stmt FROM @prepared_sql;
    EXECUTE stmt;

    OPEN cursor1;

    -- Business Logic
    cursor_loop: LOOP

        fetch cursor1 into var1, var2;

        IF no_more_rows THEN
            LEAVE cursor_loop;
        END IF;


        -- DO SOMETHING WITH var1,var2

    END LOOP cursor_loop;

    -- closing cursors
    CLOSE cursor1;

    DEALLOCATE PREPARE stmt;

    -- select only for test purposes
    select * from tmp_table;
    drop temporary table tmp_table;
END$$

DELIMITER ;

您可以按如下方式使用它:

call sp_test('select table_name, table_collation 
           from information_schema.tables where table_schema = \'mysql\';');

您将得到:

+---------------------------+-------------------+
| table_name                | table_collation   |
+---------------------------+-------------------+
| columns_priv              | utf8_bin          |
| db                        | utf8_bin          |
| event                     | utf8_general_ci   |
| func                      | utf8_bin          |
| general_log               | utf8_general_ci   |
...