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.

LOOP-01: Make sure the loop will terminate

---

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-02: Make the termination conditions of a loop obvious

---

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.

LOOP-03: Use a single LEAVE in simple loops

---

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.

LOOP-04: Use a simple loop to avoid redundant code required by a WHILE or REPEAT UNTIL loop

---

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;