MySQL Stored Procedure Programming

Name

LOOP-03: Use a single LEAVE in simple loops

Synopsis

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.