MySQL Stored Procedure Programming

Name

PRG-02: Standardize module structure using function and procedure templates

Synopsis

Once you adopt a set of guidelines for how developers should write procedures and functions, you need to help those developers follow their best practices. The bottom line is that guidelines will be followed if you make it easier to follow them than to ignore them.

For module standards, you can use either of the following approaches:

• Create a static template file that contains the generic logical structure for a procedure and/or function. Developers then copy that file to their own file, “de-genericize” the template by performing search-and-replace operations on placeholder strings with their own specific values (such as table names), and modify it from there.

• Use a program (one that you’ve written or a commercially available tool) that generates the code you want. This approach can be more flexible and can save you time, depending on how sophisticated a generator you use/create.

Example

Here’s a simple function template that reinforces the single RETURN recommendation and encourages a standard header.

    CREATE FUNCTION f_<name>
      (IN in_<parm> <datatype>)
         RETURNS <datatype>
         DETERMINISTIC
    BEGIN
       /*
        || STANDARD COPYRIGHT STATEMENT HERE
        || Author:
        ||   File:
        ||
        || Modification history:
        */

       DECLARE retval <datatype> DEFAULT <value>
       -- Put your code here

       RETURN retval;

    END

Some third-party products (Toad for MySQL, for instance) allow you to define such a template and have it automatically applied to new stored ...