How can I iterate through all rows in a MySQL table using a stored procedure?

Looping Through All Rows in a Table Using a Procedure

In MySQL, looping through all rows of a table requires creating a stored procedure. A procedure allows you to encapsulate a series of SQL statements into a single unit and execute them as a block.

Creating the Procedure

To loop through all rows of table 'A' and perform specific operations, create a procedure using the following syntax:

DROP PROCEDURE IF EXISTS ROWPERROW; -- Delete existing procedure if any

DELIMITER ;; -- Change delimiter to avoid statement-by-statement execution

CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE n INT DEFAULT 0; -- Declare a variable to count rows
  DECLARE i INT DEFAULT 0; -- Declare a variable for looping
  SELECT COUNT(*) INTO n FROM table_A; -- Get the total number of rows
  SET i = 0; -- Initialize loop counter
  WHILE i < n DO
    -- Insert the current row into table 'B'
    INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i, 1;
    SET i = i + 1; -- Increment loop counter
  END WHILE;
End;
;;

DELIMITER ; -- Reset the delimiter

Running the Procedure

Once the procedure is created, it can be called using the following syntax:

CALL ROWPERROW();

This will execute the procedure and loop through each row of table 'A', performing the specified operations.

Example

Suppose you have two tables, 'A' and 'B', with fields 'ID' and 'VAL'. The following procedure loops through all rows of table 'A' and copies the data into table 'B':

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  SELECT COUNT(*) INTO n FROM table_A;
  SET i = 0;
  WHILE i < n DO
    INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i, 1;
    SET i = i + 1;
  END WHILE;
End;
;;

DELIMITER ;

To execute the procedure and copy the data, run the following command:

CALL ROWPERROW();

The above is the detailed content of How can I iterate through all rows in a MySQL table using a stored procedure?. For more information, please follow other related articles on the PHP Chinese website!