使用CURSOR/LOOP从存储过程中返回单个集合中的数据

Question

我想在单个集合中返回数据，但我在一行中得到了10个不同的输出。我想在一套中有10行：

DROP TABLE IF EXISTS calendar;
DROP PROCEDURE IF EXISTS p_generate_snapshot;
CREATE TABLE calendar(date date);
INSERT INTO calendar(date) VALUES
('2020-11-01'),
('2020-11-02'),
('2020-11-03'),
('2020-11-04'),
('2020-11-05'),
('2020-11-06'),
('2020-11-07'),
('2020-11-08'),
('2020-11-09'),
('2020-11-10');
DELIMITER $$
CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN

DECLARE d date;
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_name CURSOR FOR SELECT * FROM calendar c WHERE date >= start_date AND date < end_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_name;
fetch_loop: LOOP
FETCH cursor_name INTO d;
IF done THEN  
LEAVE fetch_loop;
END IF;
SELECT d;

END LOOP;
CLOSE cursor_name;
END$$
DELIMITER ;
CALL p_generate_snapshot('20201101', '20201201');

Answer 1

不清楚为什么要使用光标。您的程序可以写成：

CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN  
SELECT * FROM calendar c WHERE date >= start_date AND date < end_date;
END

这将返回一个结果集，正如您所说的那样。

但是，您的示例可能被简化了，并且您需要对光标获取的行执行其他步骤。这将是使用游标的合理理由。

但我认为不可能像返回一个结果集一样返回游标处理的行。当然，在游标循环的每次迭代中，SELECT d;是不可能做到这一点的。正如您所发现的，这必然会为每行返回一个单独的结果集。

一种解决方法是在游标循环期间将行插入到临时表中，尽管这很尴尬。然后从临时表中选择作为最后一步。

CREATE PROCEDURE p_generate_snapshot(start_date date, end_date date)
BEGIN

DECLARE _d date;
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_name CURSOR FOR SELECT date FROM calendar c WHERE date >= start_date AND date < end_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE t (d DATE);
OPEN cursor_name;
fetch_loop: LOOP
FETCH cursor_name INTO _d;
IF done THEN  
LEAVE fetch_loop;
END IF;
INSERT INTO t SET d = _d;

END LOOP;
CLOSE cursor_name;
SELECT d FROM t;
DROP TABLE t;
END

---

就这一点而言，我不知道为什么要使用过程，而不是在客户端应用程序中运行SELECT语句。这将解决这两个问题——一个接一个地处理行，但仍将其视为单个查询结果。

我几乎从不在MySQL中使用存储过程。我发现他们真的比他们的价值更麻烦。我在这里发布了原因：https://www.quora.com/What-are-the-reasons-not-to-use-or-not-use-stored-procedures/answer/Bill-Karwin