How Can I Loop Over MySQL Result Sets Using Stored Procedures?

Looping Over Result Sets in MySQL: A Procedure-Based Approach

Programmers often need to process the results of MySQL queries in a loop. This task is typically accomplished using the mysql_fetch_assoc function in PHP or similar methods in other programming languages. However, it is also possible to create a stored procedure in MySQL that loops over the results of a query.

To achieve this, one can employ a stored procedure template like the following:

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE var1 CHAR(16); 
  DECLARE var2 INT;
  DECLARE var3 VARCHAR(50);
  
  DECLARE curs CURSOR FOR SELECT something FROM somewhere WHERE some stuff;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  DROP TEMPORARY TABLE IF EXISTS tblResults;
  CREATE TEMPORARY TABLE IF NOT EXISTS tblResults  (
    Fld1 type,
    Fld2 type,
    ...
  );

  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO var1, var2, var3;

    IF whatever_filtering_desired
       INSERT INTO tblResults VALUES (var1, var2, var3);
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT * FROM tblResults;
END

Considerations:

• It is recommended to parameterize the query, particularly the search criteria, to increase the stored procedure's flexibility.
• Consider passing a session ID in multi-session scenarios to ensure unique temporary table names.
• Although cursors provide a procedural approach to loop through results, their use may be unnecessary or performance-limiting. Exploring purely declarative SQL solutions is advised.

The above is the detailed content of How Can I Loop Over MySQL Result Sets Using Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!