Looping Over Result Sets in MySQL

Question

I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether. Effectively, I want to implement this:

$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
}

Only, I want it only in MySQL, so it can be called as a procedure. I know that for triggers, there is the FOR EACH ROW ... syntax, but I can't find mention of anything like this for use outside of the CREATE TRIGGER ... syntax. I have read through some of the looping mechanisms in MySQL, but so far all I can imagine is that I would be implementing something like this:

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

Although even this is somewhat hazy in my mind.

For reference, though I don't think it's necessarily relevant, the initial query will be joining four tables together to form a model of hierarchal permissions, and then based on how high up the chain a specific permission is, it will retrieve additional information about the children to which that permission should be inherited.

Answer 1

Something like this should do the trick (However, read after the snippet for more info)

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,, b;

    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

A few things to consider...

Concerning the snippet above:

• may want to pass part of the query to the Stored Procedure, maybe particularly the search criteria, to make it more generic.
• If this method is to be called by multiple sessions etc. may want to pass a Session ID of sort to create a unique temporary table name (actually unnecessary concern since different sessions do not share the same temporary file namespace; see comment by Gruber, below)
• A few parts such as the variable declarations, the SELECT query etc. need to be properly specified

More generally: trying to avoid needing a cursor.

I purposely named the cursor variable curs[e], because cursors are a mixed blessing. They can help us implement complicated business rules that may be difficult to express in the declarative form of SQL, but it then brings us to use the procedural (imperative) form of SQL, which is a general feature of SQL which is neither very friendly/expressive, programming-wise, and often less efficient performance-wise.

Maybe you can look into expressing the transformation and filtering desired in the context of a "plain" (declarative) SQL query.

Answer 2

Use cursors.

A cursor can be thought of like a buffered reader, when reading through a document. If you think of each row as a line in a document, then you would read the next line, perform your operations, and then advance the cursor.