这里我要变量ISSUDAYS中的20条记录。但是我的游标只返回第一个reord。列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;发布于 2014-03-14 06:56:29
尝试创建一个临时表并插入。然后在完成游标后,从temp表中查询一次。
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发布于 2014-03-14 07:31:21
是的,要在这里显示所有的记录,这些记录是游标正在抓取的,需要创建一个临时表。声明日期为今天;
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;https://stackoverflow.com/questions/22396634
复制相似问题