Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from MySQL Stored Procedure Programming

Name

PRG-05: Avoid deep nesting of conditionals and loops

Synopsis

Many studies have confirmed that excessive nesting of IF, CASE, or LOOP structures leads to code that is difficult to understand. More than two or three levels of nesting is probably undesirable.

Consider the following logic:

    IF  v_state='CA' THEN
        IF  v_quantity > 100 THEN
          IF  v_customer_status='A' THEN
            IF  v_product_code='X' THEN
                SET  v_discount=.04;
            ELSEIF  v_product_code='Y' THEN
                SET  v_discount=.04;
            ELSE
                SET  v_discount=.01;
            END IF;
          ELSE
            SET  v_discount=0;
          END IF;
        ELSEIF  v_quantity > 50 THEN
          SET  v_discount=.1;
        . . . More logic . . .
      END IF;

It's fairly difficult to determine which set of conditions is applied to any particular discount. For instance, consider the highlighted line above—it takes a bit of puzzling to work out which states, quantities, and so on are associated with this discount: and that is with the vast majority of the logic removed. There are a few possible solutions to this deep nesting:

  • Including multiple conditions in each IF or ELSEIF clause: For instance, we might test for a specific combination of state, quantity, and status on the one line.

  • Removing parts of the logic to separate subroutines: For instance, we might create separate subroutines that calculate discounts for each state.

  • Creating a data-driven solution: For instance, in the above example it would probably be preferable to create a table that includes the discount for each combination of values.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page