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

User · Accepted Answer

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.

User · Answer

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

[mysql] Looping Over Result Sets in MySQL

Examples related to mysql

Examples related to loops

Examples related to stored-procedures

Examples related to cursor