Loop Processing

Follow the best practices in this section when you are performing iterative processing in stored programs using the various looping controls: LOOP, WHILE, and REPEAT.

One of the most annoying and potentially disruptive bugs that can be created in any language is the inadvertent infinite loop.

Making sure that a loop will terminate requires that you simulate all possible paths through the loop and assure yourself that the loop will always encounter an exit condition. If the loop does not terminate, it will likely consume excessive CPU and/or memory resources until it is manually terminated by the system administrator. In a worst-case scenario, the MySQL server itself may be terminated.

Example

The following stored procedure calculates the number of prime numbers less than the supplied input parameter. It's part of a larger routine that we plan to put in action when we're next contacted by extraterrestrial intelligences that announce their presence by broadcasting prime numbers at planet Earth.

CREATE PROCEDURE check_for_primes(in_limit INT) BEGIN DECLARE i INT DEFAULT 2; DECLARE j INT DEFAULT 1; DECLARE n_primes INT DEFAULT 0; DECLARE is_prime INT DEFAULT 0; REPEAT -- See if i is a prime number SET j=2; SET is_prime=1; divisors: WHILE(j< i) DO IF MOD(i,j)=0 THEN SET is_prime=0; LEAVE divisors; END IF; SET j=j+1; END WHILE; IF is_prime THEN SET n_primes=n_primes+1; END IF; -- Move onto the next number IF (MOD(i,2)=0) THEN SET i=i+1; ELSE -- Next number is even, no need -- to check for it as a prime SET i=i+2; END IF; UNTIL (i=in_limit) END REPEAT; SELECT CONCAT(n_primes,' prime numbers <= ',in_limit); END$$

Unfortunately, this routine has a bug that will lead to an infinite loop if the input number is even. A clever programmer altered the loop increment value so that even numberswhich can never be primewere skipped as the loop incremented. Unfortunately, the UNTIL loop contains an equality check, i=in_limit, that will never be satisfied if the input parameter is even, and hence the loop will never terminate.

This bug could have been detected or averted in a number of ways:

• Walk-through of the program's algorithm
• Testing of the routine with a variety of inputs (including, of course, even numbers)
• Adoption of a defensive programming philosophy that could have led to the inclusion of a more robust i>in_limit condition in the UNTIL clause

Loop logic is easier to determine if all the control logic is in one place, either in the WHILE or UNTIL clauses or in a LEAVE statement within the loop. It's particularly confusing to include a RETURN statement within a loop.

To that end, we suggest that you avoid LEAVE or RETURN statements within WHILE or REPEAT UNTIL loops.

Example

In the following example, borrowed from the prime number routine in the preceding section, a WHILE loop contains a LEAVE clausethere are two ways for the loop to terminate, and this makes the code harder to analyze and trace:

SET j=2; SET is_prime=1; divisors: WHILE(j< i) DO IF MOD(i,j)=0 THEN SET is_prime=0; LEAVE divisors; END IF; SET j=j+1; END WHILE;

One way to improve the readability of the loop would be to move all of the termination logic into the WHILE clause:

SET j=2; SET is_prime=1; divisors: WHILE(j< i AND is_prime=1) DO IF MOD(i,j)=0 THEN SET is_prime=0; END IF; SET j=j+1; END WHILE;

Alternatively, we could employ a simple loop and place all termination logic within the loop.

This best practice is another variation on "one way in, one way out." It suggests that, whenever possible, you consolidate all exit logic in your simple loop to a single LEAVE statement.

Example

Here is another variant on our prime counting loop. It contains some new logic to handle the special cases of 1 and 2 (1 is not prime; 2 is prime).

SET j=2; SET is_prime=1; divisors: LOOP IF (j=1) THEN SET is_prime=0; LEAVE divisors; END IF; IF (j=2) THEN SET is_prime=1; LEAVE divisors; END IF; IF MOD(i,j)=0 THEN SET is_prime=0; END IF; SET j=j+1; IF (is_prime=0 OR j>=i ) THEN LEAVE divisors; END IF; END LOOP divisors;

The multiple LEAVE statements make it difficult for us to work out which segments of the code are actually executed for any given number. A rewrite that relies on a single LEAVE looks like this:

SET j=2; SET is_prime=1; divisors: LOOP IF (i=1) THEN SET is_prime=0; ELSEIF (i=2) THEN SET is_prime=1; ELSEIF MOD(i,j)=0 THEN SET is_prime=0; SELECT i,'is divisible by',j; END IF; IF (i=2 OR is_prime=0 OR j+1>=i ) THEN LEAVE divisors; END IF; SET j=j+1; END LOOP divisors;

Now we have a single place in the code where we make the decision to leave the loop, and, consequently, our code is more readable and robust.

This guideline is particularly relevant when you are writing cursor loops.

The structure of MySQL cursors, and the necessity of setting an indicator variable to detect the end of the cursor, means that you usually want to execute the cursor loop at least once. You will then continue executing the loop until the indicator variable changes.

This sounds like a perfect opportunity to apply the REPEAT UNTIL loop. So as you start to create the program, you create a structure that looks like this:

DECLARE dept_csr CURSOR FOR SELECT department_name FROM departments; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; OPEN dept_csr; REPEAT FETCH dept_csr INTO v_department_name; UNTIL (no_more_departments) END REPEAT; CLOSE dept_csr; SET no_more_departments=0;

Of course, you always want to do something with the data fetched from a cursor, but you need to make sure that you don't try to process data after the last row has been returned. So in order to keep the REPEAT loop, you create an IF structure to enclose your processing:

DECLARE dept_csr CURSOR FOR SELECT department_name FROM departments; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; OPEN dept_csr; REPEAT FETCH dept_csr INTO v_department_name; IF (no_more_departments=0) THEN SET v_count= v_count+1; END IF; UNTIL (no_more_departments) END REPEAT; CLOSE dept_csr; SET no_more_departments=0;

The problem with this solution is that you now have redundant tests to determine if you have reached the end of the cursor. If you change the CONTINUE handler, you will have to change your code in two places.

The code would be simpler and more maintainable if the test were conducted only once:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1; OPEN dept_csr; dept_loop: LOOP FETCH dept_csr INTO v_department_name; IF (no_more_departments) THEN LEAVE dept_loop; END IF; SET v_count= v_count+1; END LOOP; CLOSE dept_csr; SET no_more_departments=0;