问题描述
我正在尝试在mysql中学习存储过程.我知道下面的示例不需要SP,但这只是出于我的学习目的-只是为了了解如何循环遍历行以及在行内选择特定的列数据.
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.
假设该表上的SELECT从我的表中返回5条具有以下结构的记录-
Assume that a SELECT on the table returns 5 records from my table with following structure -
表名称:t1列:c1,c2,c3
table name: t1columns: c1,c2,c3
我想遍历5行中的每行,对于每行,我想将列单元格数据存储到SP变量varc1,varc2,varc3中,这样varc1 = c1,varc2 = c2,varc3 = c3.我将在循环内使用这些varc1,varc2,varc3进行进一步的计算.
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.
我假设需要一个存储过程,但是我没有采用这种方法进行行迭代,然后从循环中考虑的行中的每一列中选择数据.
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.
是mysql/存储过程的新手.谢谢!
quite new to mysql/ stored procedure.thank you!
推荐答案
您似乎想知道的是,对于大多数问题而言,这是一种效率特别低下的解决方案.但是,由于您是专门要求它的,而且(偶尔)是解决问题的唯一方法(或者您个人有时间弄清楚如何解决问题的唯一方法).这是您可能会做的事情:
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:
创建您的t1表,例如:
Create your t1 table eg:
CREATE TABLE t1(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, C1 INT, C2 INT, C3 INT);
执行您描述的SELECT,例如:
Carry out your SELECT that you describe eg:
INSERT INTO t1 SELECT NULL,c1,c2,c3 FROM the_table;
现在这是您的按行行"ReeBAR"低效率存储过程:
Now here's your Row-By-Agonising-Row "ReeBAR" low efficiency stored procedure:
为过程准备空间并设置定界符:
Prepare space for your procedure and set the delimiter:
DROP PROCEDURE IF EXISTS ReeBAR;
DELIMITER ;;
编写您的程序:
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;
或者,如果您的id列不连续,则可以使用以下游标形式.
Alternatively if your id column in not sequential you can use the follwoing cursor form.
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 ;
最后运行您的ReBAR程序
Finally to run your ReBAR procedure
CALL ReeBAR();
(未经测试的代码)
这篇关于循环遍历结果行并将列数据存储到存储过程变量mysql中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!