Looping Over Result Sets in MySQL

user_173925 0
Viewed 180081

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.

database-cursor stored-procedures mysql loops
3 Answers

Using a cursor within a stored procedure. Prepare the SQL Query

SELECT id FROM employee where department_id = 1;

Create the cursor which will hold the result set returned by the SQL Query.

DECLARE BonusDistributionCursor CURSOR FOR SELECT id FROM employee where department_id = 1;

To have a safe exit when fetching a row from cursor does not return any result then declare a handler called NOT FOUND and set value to a declared variable

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Open the Cursor before you can fetch the next row from the cursor.

OPEN BonusDistributionCursor;

Fetch the next row pointed by the cursor and move the cursor to next row after that.

FETCH BonusDistributionCursor INTO employeeId;

Run the desired business logic according to the usecase required.

DELIMITER $$
CREATE PROCEDURE distributeYearlyBonus (IN departmentId VARCHAR(2))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE empId VARCHAR(TEXT) DEFAULT "";
DECLARE BonusDistributionCursor CURSOR FOR SELECT id FROM employee where department_id = departmentId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN BonusDistributionCursor;
addBonus: LOOP
   FETCH BonusDistributionCursor INTO empId;
   IF finished = 1 THEN 
      LEAVE addBonus;
   END IF;
INSERT INTO `bonus_paid_details` (`employee_id`, `year`, `datetime`) VALUES (empId, YEAR(CURDATE());, now());
END LOOP addBonus;
CLOSE BonusDistributionCursor;
END$$
DELIMITER ;

Execute the above script and you will find a new Stored Procedure created.

Call or Invoke the Stored Procedure by inputing the departmentId which will receive the bonus amount.

CALL BonusDistributionCursor(1);

Hope this explains "How to iterate using Cursors used within Stored Procedure"

user_99155940
Related