MySQL Stored Procedure Programming

Name

PRG-04: Avoid side-effects in your programs

Synopsis

Build lots of individual programs. Design each program so that it has a single, clearly defined purpose. That purpose should, of course, be expressed in the program’s name, as well as in the program header.

Avoid throwing extraneous functionality inside a program. Such statements are called side-effects and can cause lots of problems for people using your code—which means your code won’t get used, except perhaps as source for a cut-and-paste session (or—in hardcopy form—for kindling).

Example

Here’s a program that by name and “core” functionality displays information about all books published within a certain date range:

    CREATE PROCEDURE book_details (
      in_start_date DATE,
      in_end_date   DATE)
    BEGIN
      DECLARE  v_title, v_author VARCHAR(60);
      DECLARE  v_last_book,  v_book_id INT DEFAULT 0;

      DECLARE book_cur CURSOR FOR
          SELECT book_id,title,author
            FROM books
           WHERE date_published BETWEEN in_start_date
                     AND in_end_date;

      OPEN book_cur;
      book_loop:LOOP
        FETCH book_cur INTO  v_book_id, v_title, v_author;
        IF  v_last_book THEN LEAVE book_loop; END IF;

        CALL details_show( v_title, v_author);
        CALL update_borrow_history ( v_book_id);
      END LOOP;
    END$$

Notice, however, that it also updates the borrowing history for that book. Now, it might well be that at this point in time the display_book_info procedure is called only when the borrowing history also needs to be updated, justifying to some extent the way this program is written.

However, regardless of current ...