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 ) THENLEAVE
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.
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.