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 · 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

User · 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

[mysql] Looping Over Result Sets in MySQL

Examples related to mysql

Examples related to loops

Examples related to stored-procedures

Examples related to cursor