首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL游标仅返回一行

问MySQL游标仅返回一行
EN

Stack Overflow用户
提问于 2014-03-14 05:10:12
回答 2查看 2.7K关注 0票数 1

这里我要变量ISSUDAYS中的20条记录。但是我的游标只返回第一个reord。列issue_book.issue_date由20条记录组成..!请帮帮我。提前谢谢你。

代码语言:javascript
复制
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;
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2014-03-14 06:56:29

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

代码语言:javascript
复制
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
票数 3
EN

Stack Overflow用户

发布于 2014-03-14 07:31:21

是的,要在这里显示所有的记录,这些记录是游标正在抓取的,需要创建一个临时表。声明日期为今天;

代码语言:javascript
复制
DECLARE DAYS INT DEFAULT 1;
DECLARE COUNTER, done INT DEFAULT FALSE;
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;  
CREATE TEMPORARY TABLE temp
(
id int not null auto_increment primary key, 
tempdate DATE
);
OPEN CUR1;
READ_LOOP: LOOP
FETCH CUR1 INTO ISSUEDAYS;
SET COUNTER=COUNTER+1;
IF done THEN LEAVE READ_LOOP;
END IF;
  INSERT INTO temp(tempdate)
  SELECT ISSUEDAYS AS tempdate;
    END LOOP;
CLOSE CUR1;
SELECT * FROM temp;
DROP TABLE temp;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/22396634

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档