我想返回一个在存储过程中动态创建的集合(为了简单起见,假设该集合是在循环中计算的偶数列表)。
我使用过的一些数据库具有SUSPEND
操作,该操作将OUT
参数的当前值添加到过程的结果集中,如下所示:
DELIMITER $
CREATE PROCEDURE EvenNumbers(
IN n INT, OUT NUM INT )
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET NUM = i;
SUSPEND; /* adds a row with a value 'i' for column NUM to the result set */
SET i = i+2;
END WHILE;
END$
DELIMITER ;
SUSPEND
是MySQL中的一个关键字,但我认为它不会做同样的事情,因为这不起作用(Error: Unexpected 'SUSPEND'
)。
那么,如何从 MySQL 中的存储过程中逐一返回一组行呢?
Alex,您可以在存储过程中创建一个临时表(这是完全可以的),将计算结果存储在其中,并在完成计算后返回临时表的内容。因此,使用过程返回的正常结果集,而不是 OUT 参数。
我知道你想要一个偶数生成器?这是纯粹的MySQL,不使用存储过程。
首先,您需要通过下面的查询生成大量数字。此查询生成 1 到 10000 条记录。
查询
SELECT
@row := @row + 1 AS ROW
FROM
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row2
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row3
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row4
CROSS JOIN (
SELECT @row := 0
) init_user_param
主查询。
查询
SELECT
*
FROM (
SELECT
@row := @row + 1 AS ROW
FROM
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row2
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row3
CROSS JOIN (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) row4
CROSS JOIN (
SELECT @row := 0
) init_user_param
)
ROWS
WHERE
ROWS.ROW BETWEEN 1 AND 10
AND
ROWS.ROW MOD 2 = 0
结果
row
--------
2
4
6
8
10