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