How to Loop Through Rows in a MySQL Table: Procedures vs. Cursors?

Looping Through Rows in a MySQL Table

In MySQL, looping through rows of a table can be achieved using a procedure or cursor-based approach. This article explores both methods based on a specific scenario involving copying data from Table A to Table B.

Procedure-based Approach

A procedure encapsulates a set of operations that can iterate over rows of a table. Here's an example procedure:

DELIMITER ;;

CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  SELECT COUNT(*) FROM table_A INTO n;
  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;
;;

After creating the procedure, you can execute it using CALL ROWPERROW();.

Cursor-based Approach

Cursors allow for iterating over a set of rows returned by a query. Here's a cursor-based approach:

DELIMITER ;;

CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
  DECLARE cursor_ID INT;
  DECLARE cursor_VAL VARCHAR;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_i CURSOR FOR SELECT ID,VAL FROM table_A;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_i;
  read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, cursor_VAL;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO table_B(ID, VAL) VALUES(cursor_ID, cursor_VAL);
  END LOOP;
  CLOSE cursor_i;
END;
;;

Remember to declare variables with appropriate data types and handle any errors or exceptions.

Considerations

Both the procedure-based and cursor-based approaches can be used to loop through table rows. However, procedure-based methods are less flexible than cursors. Cursors allow for more complex operations, such as updating multiple rows at once.

For performance reasons, set-based queries are typically preferable to looping mechanisms. However, for complex tasks or scenarios where real-time updates are required, looping approaches may be necessary.

The above is the detailed content of How to Loop Through Rows in a MySQL Table: Procedures vs. Cursors?. For more information, please follow other related articles on the PHP Chinese website!