How Can I Loop Through Result Sets in MySQL?

Looping Over Result Sets in MySQL

This article explores the concept of looping over result sets in MySQL, a fundamental operation in database programming. We aim to emulate the functionality of the following PHP code:

$result = mysql_query("SELECT something FROM somewhere WHERE some stuff");
while ($row = mysql_fetch_assoc($result)) {
    // check values of certain fields, decide to perform more queries, or not
    // tack it all into the returning result set
}

Cursor-Based Approach

MySQL provides cursors for iterating over result sets. However, it lacks capabilities like FOR EACH ROW syntax for trigger implementations. Despite this, it is possible to create a cursor-based loop using the following approach:

SET @S = 1;
LOOP
    SELECT * FROM somewhere WHERE some_conditions LIMIT @S, 1
    -- IF NO RESULTS THEN
    LEAVE
    -- DO SOMETHING
    SET @S = @S + 1;
END LOOP

Stored Procedure with Cursor

A more structured way to implement this logic is through a stored procedure that employs a cursor. Here is an example:

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE var1 CHAR(16);    -- or approriate type
  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
       -- here for whatever_transformation_may_be_desired
       INSERT INTO tblResults VALUES (var1, var2, var3);
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT * FROM tblResults;
END

Considerations

While cursors can be helpful for implementing complex business rules, they come with certain drawbacks. Therefore, it is recommended to consider alternative methods, such as expressing the desired transformations and filters in a single, declarative SQL query.

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