MySQL游标只返回一行

Question

这里我想要变量 ISSUDAYS 中的 20 条记录。但我的光标只返回第一个记录。 issue_book.issue_date 列包含 20 条记录..!请帮我。提前谢谢你。

DECLARE RESULTDAYS,ISSUEDAYS,TODAYS DATE;
DECLARE done VARCHAR(10);
DECLARE DAYS INT DEFAULT 1;
DECLARE CUR1 CURSOR FOR SELECT issue_book.issue_date FROM bookstore.issue_book WHERE   return_date IS NULL AND status = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;       
OPEN CUR1;
READ_LOOP: LOOP
 FETCH CUR1 INTO ISSUEDAYS;
 IF done THEN
 LEAVE READ_LOOP;
 END IF;
  SELECT ISSUEDAYS;
 END LOOP;
CLOSE CUR1;

Answer 1

尝试创建一个临时表并插入。然后在游标完成后从临时表中查询一次。

BEGIN
DECLARE RESULTDAYS,ISSUEDAYS,TODAYS DATE;
DECLARE done BOOL DEFAULT FALSE;
DECLARE DAYS INT DEFAULT 1;
DECLARE CUR1 CURSOR FOR SELECT issue_book.issue_date FROM bookstore.issue_book WHERE return_date IS NULL AND status = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

DROP TABLE IF EXISTS mytemptable;
CREATE TEMPORARY TABLE IF NOT EXISTS mytemptable(issue_date DATE);

OPEN CUR1;

READ_LOOP: LOOP
FETCH CUR1 INTO ISSUEDAYS;
IF done THEN
LEAVE READ_LOOP;
END IF;

INSERT INTO mytemptable SELECT ISSUEDAYS;
END LOOP;
CLOSE CUR1;
SELECT * FROM mytemptable;
END