循环遍历结果行并将列数据存储到存储过程变量 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12877244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
looping through result rows and storing column data into stored procedure variables mysql
提问by kallakafar
I m trying to learn stored procedure in mysql. I know the below example does not need SP, but this is ONLY for my learning purpose - just to understand how to loop through rows, and pick specific column data inside the row.
我正在尝试学习 mysql 中的存储过程。我知道下面的示例不需要 SP，但这仅用于我的学习目的 - 只是为了了解如何遍历行，并选择行内的特定列数据。
Assume that a SELECT on the table returns 5 records from my table with following structure -
假设表上的 SELECT 从我的表中返回 5 条记录，结构如下 -
table name: t1 columns: c1,c2,c3
表名：t1 列：c1,c2,c3
i would like to loop through each of the 5 rows, and for each row, i would like to store the column cell data into SP variables varc1,varc2,varc3, so that varc1 = c1, varc2 = c2, varc3 = c3. i would be using these varc1,varc2,varc3 for further computation while within the loop.
我想遍历 5 行中的每一行，对于每一行，我想将列单元格数据存储到 SP 变量 varc1、varc2、varc3 中，以便 varc1 = c1、varc2 = c2、varc3 = c3。我将在循环中使用这些 varc1、varc2、varc3 进行进一步计算。
i am assuming that a stored procedure is required, but i am not getting the approach in doing the row iteration and then picking data from each column in the row under consideration in the loop.
我假设需要一个存储过程，但我没有得到执行行迭代的方法，然后从循环中考虑的行中的每一列中选取数据。
quite new to mysql/ stored procedure. thank you!
对 mysql/ 存储过程很陌生。谢谢你！
回答by Mr Purple
What your asking for, as you seem to know, is a particularly inefficient solution to most problems. However, since you specifically asked for it, and since (very occasionally) it's the only way to solve a problem (or the only way you personally have time to figure out how to solve it). Here's how you might do what you ask:
正如您似乎知道的那样，您要求的是对大多数问题的特别低效的解决方案。但是，由于您特意要求它，并且（偶尔）这是解决问题的唯一方法（或者您个人有时间弄清楚如何解决它的唯一方法）。以下是您可以按照您的要求执行的方法：
Create your t1 table eg:
创建您的 t1 表，例如：
CREATE TABLE t1(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, C1 INT, C2 INT, C3 INT);
Carry out your SELECT that you describe eg:
执行您描述的选择，例如：
INSERT INTO t1 SELECT NULL,c1,c2,c3 FROM the_table;
Now here's your Row-By-Agonising-Row "ReeBAR" low efficiency stored procedure:
现在这是你的 Row-By-Agonising-Row "ReeBAR" 低效率存储过程：
Prepare space for your procedure and set the delimiter:
为您的过程准备空间并设置分隔符：
DROP PROCEDURE IF EXISTS ReeBAR;
DELIMITER ;;
Write your procedure:
写你的程序：
CREATE PROCEDURE ReeBAR()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE varc1 INT DEFAULT 0;
DECLARE varc2 INT DEFAULT 0;
DECLARE varc3 INT DEFAULT 0;
SELECT COUNT(*) into n FROM t1;
set i=1;
WHILE i<=n DO
SELECT c1 FROM t1 WHERE id=i INTO varc1;
SELECT c2 FROM t1 WHERE id=i INTO varc2;
SELECT c3 FROM t1 WHERE id=i INTO varc3;
--queries which couldnt be made into set based queries go here---
SET i = i + 1;
END WHILE;
End;
Alternatively if your id column in not sequential you can use the follwoing cursor form.
或者，如果您的 id 列不是连续的，您可以使用以下游标形式。
CREATE PROCEDURE ReeBAR()
BEGIN
DECLARE cursor_ID INT;
DECLARE cursor_VAL VARCHAR;
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_i CURSOR FOR SELECT c1,c2,c3 FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE varc1 INT DEFAULT 0;
DECLARE varc2 INT DEFAULT 0;
DECLARE varc3 INT DEFAULT 0;
OPEN cursor_i;
read_loop: LOOP
FETCH cursor_i INTO varc1, varc2, varc3;
IF done THEN
LEAVE read_loop;
END IF;
--queries which couldnt be made into set based queries go here---
END LOOP;
CLOSE cursor_i;
END;
Don't forget to "end" the procedure you use and reset the delimiter
不要忘记“结束”您使用的过程并重置分隔符
;;
DELIMITER ;
Finally to run your ReBAR procedure
最后运行你的 ReBAR 程序
CALL ReeBAR();
(code untested)
（代码未经测试）
回答by FSP
Use CURSORS and a loop to iterate over it. http://dev.mysql.com/doc/refman/5.0/en/cursors.html. Hope this helps!
使用 CURSORS 和一个循环来迭代它。http://dev.mysql.com/doc/refman/5.0/en/cursors.html。希望这可以帮助！
回答by Ankur Kumar Singh
For looping through the record you can refer to MySQL Cursor Loopor if you want a general loop in mysql stored procedure you can refer to Mysql Store Procedure
对于循环记录，您可以参考MySQL Cursor Loop或者如果您想要在 mysql 存储过程中进行一般循环，您可以参考Mysql Store Procedure
