mysql存储过程行变量,循环遍历结果行并将列数据存储到存储过程变量mysql中

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.

Assume that a SELECT on the table returns 5 records from my table with following structure -

table name: t1

columns: 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.

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!

解决方案

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:

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:

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.

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

CALL ReeBAR();

(code untested)