如何在MySQL中使用游标循环访问表？ [英] How to loop through a table using a cursor in MySQL?

问题描述

我在我的数据库中有以下表，我写了以下存储过程来循环表。

I have following table in my database and I wrote following stored procedure to loop through the table.

当我调用这个存储过程时，我只得到一个记录。

When I call this stored procedure, I get only one record.

我所做的错误是什么，如何解决？

What could be the error I have done, and how can this be fixed?

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| date   | date         | NO   |     | NULL    |       |
| inQty  | decimal(5,2) | NO   |     | 0.00    |       |
| outQty | varchar(45)  | YES  |     | 0.0     |       |
+--------+--------------+------+-----+---------+-------+


-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_balance`()
BEGIN

DECLARE vDate DATE DEFAULT '0000-00-00';
DECLARE vInQty DECIMAL DEFAULT 0.0;
DECLARE tOutQty DECIMAL DEFAULT 0.0;
DECLARE balance DECIMAL DEFAULT 0.0;

DECLARE vvDate DATE DEFAULT '0000-00-00';

DECLARE flag INT DEFAULT 0;

DECLARE tCursor CURSOR FOR SELECT * FROM new_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

OPEN tCursor;

REPEAT
FETCH tCursor INTO vDate, vInQty, tOutQty;

 SELECT vDate, vInQty, tOutQty;


UNTIL flag
END REPEAT;

CLOSE tCursor;

END

推荐答案

请参阅以下文章
http：//www.databasejournal。 com / features / mysql / mysql-cursors-and-loops.html

和
http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html

这篇关于如何在MySQL中使用游标循环访问表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！