MySQL Stored Procedure Programming

A Quick Tour

Let’s look at a few quick examples that demonstrate some key elements of both the structure and the functionality of MySQL’s stored program language. For a full tutorial, see Chapter 2.

Integration with SQL

One of the most important aspects of MySQL’s stored program language is its tight integration with SQL. You don’t need to rely on intermediate software “glue,” such as ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity), to construct and execute SQL statements in your stored program language programs. Instead, you simply write the UPDATE, INSERT, DELETE, and SELECT statements directly into your code, as shown in Example 1-1.

Example 1-1. Embedding SQL in a stored program

1  CREATE PROCEDURE example1(  )
2  BEGIN
3    DECLARE 
 l_book_count INTEGER;
4
5    SELECT COUNT(*)
6      INTO l_book_count
7      FROM books
8     WHERE author LIKE '%HARRISON,GUY%';
9
10   SELECT CONCAT('Guy has written (or co-written) ',
11          l_book_count ,
12          ' books.');
13
14    -- Oh, and I changed my name, so...
15    UPDATE books
16       SET author = REPLACE (author, 'GUY', 'GUILLERMO')
17     WHERE author LIKE '%HARRISON,GUY%';
18
19 END

Let’s take a more detailed look at this code in the following table:

Line(s)	Explanation
1	This section, the header of the program, defines the name (example1) and type (PROCEDURE) of our stored program.
2	This BEGIN keyword indicates the beginning of the program body, which contains the declarations and executable code that constitutes the procedure. If the program body contains more than one statement (as in this program), the multiple statements are enclosed in a BEGIN-END block.
3	Here we declare an integer variable to hold the results of a database query that we will subsequently execute.
5-8	We run a query to determine the total number of books that Guy has authored or coauthored. Pay special attention to line 6: the INTO clause that appears within the SELECT serves as the “bridge” from the database to the local stored program language variables.
10-12	We use a simple SELECT statement (e.g., one without a FROM clause) to display the number of books. When we issue a SELECT without an INTO clause, the results are returned directly to the calling program. This is a non-ANSI extension that allows stored programs to easily return result sets (a common scenario when working with SQL Server and other RDBMSs).
14	This single-line comment explains the purpose of the UPDATE.
15-17	Guy has decided to change the spelling of his first name to “Guillermo”— he’s probably being stalked by fans of his Oracle book—so we issue an UPDATE against the books table. We take advantage of the built-in REPLACE function to locate all instances of “GUY” and replace them with “GUILLERMO”.

Control and Conditional Logic

Of course, real-world applications are full of complex conditions and special cases, so you are unlikely to be able to simply execute a series of SQL statements. The stored program language offers a full range of control and conditional statements so that we can control which lines of our programs actually run under a given set of circumstances. These include:

IF and CASE statements

Both of these statements implement conditional logic with different structures. They allow you to express logic such as “If the page count of a book is greater than 1000, then . . . “.

A full complement of looping and iterative controls

These include the simple loop, the WHILE loop, and the REPEAT UNTIL loop.

Example 1-2, a procedure that pays out the balance of an account to cover outstanding bills, demonstrates some of the control statements of MySQL.

Example 1-2. Stored procedure with control and conditional logic

1  CREATE PROCEDURE pay_out_balance
2       (account_id_in INT)
3
4  BEGIN
5
6  DECLARE l_balance_remaining NUMERIC(10,2);
7
8  payout_loop:LOOP
9    SET l_balance_remaining = account_balance(account_id_in);
10
11    IF l_balance_remaining < 1000 THEN
12     LEAVE payout_loop;
13
14    ELSE
15      CALL apply_balance(account_id_in, l_balance_remaining);
16    END IF;
17
18  END LOOP;
19
20 END

Let’s take a more detailed look at this code in the following table:

Line(s)	Explanation
1-3	This is the header of our procedure; line 2 contains the parameter list of the procedure, which in this case consists of a single incoming value (the identification number of the account).
6	Declare a variable to hold the remaining balance for an account.
8-18	This simple loop (named so because it is started simply with the keyword LOOP, as opposed to WHILE or REPEAT) iterates until the account balance falls below 1000. In MySQL, we can name the loop (line 8, payout_loop), which then allows us to use the LEAVE statement (see line 12) to terminate that particular loop. After leaving a loop, the MySQL engine will then proceed to the next executable statement following the END LOOP; statement (line 18).
9	Call the account_balance function (which must have been previously defined) to retrieve the balance for this account. MySQL allows you to call a stored program from within another stored program, thus facilitating reuse of code. Since this program is a function, it returns a value and can therefore be called from within a MySQL SET assignment.
11-16	This IF statement causes the loop to terminate if the account balance falls below $1,000. Otherwise (the ELSE clause), it applies the balance to the next charge. You can construct much more complex Boolean expressions with ELSEIF clauses, as well.
15	Call the apply_balance procedure. This is an example of code reuse; rather than repeating the logic of apply_balance in this procedure, we call a common routine.

Stored Functions

A stored function is a stored program that returns a single value and that can be used whenever a built-in function can be used—for example, in a SQL statement. Example 1-3 returns the age of a person in years when provided with a date of birth.

Example 1-3. A stored function to calculate age from date of birth

1 CREATE FUNCTION f_age (in_dob datetime) returns int
2   NO SQL
3 BEGIN
4   DECLARE l_age INT;
5   IF DATE_FORMAT(NOW(  ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN
6      -- This person has had a birthday this year
7      SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y');
8    ELSE
9      -- Yet to have a birthday this year
10      SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
11   END IF;
12   RETURN(l_age);

END;

Let’s step through this code in the following table:

Lines(s)	Explanation
1	Define the function: its name, input parameters (a single date), and return value (an integer).
2	This function contains no SQL statements. There’s some controversy about the use of this clause —see Chapters 3 and 10 for more discussion.
4	Declare a local variable to hold the results of our age calculation.
5-11	This IF-ELSE-END IF block checks to see if the birth date in question has occurred yet this year.
7	If the birth date has, in fact, passed in the current year, we can calculate the age by simply subtracting the year of birth from the current year.
10	Otherwise (i.e., the birth date is yet to occur this year), we need to subtract an additional year from our age calculation.
12	Return the age as calculated to the calling program.

We can use our stored function wherever a built-in function would be permitted—within another stored program, in a SET statement, or, as shown in Example 1-4, within a SQL statement.

Example 1-4. Using a stored function within a SQL statement (continued)

mysql> SELECT firstname,surname, date_of_birth, f_age(date_of_birth) AS age
    ->   FROM employees LIMIT 5;
+-----------+---------+---------------------+------+
| firstname | surname | date_of_birth       | age  |
+-----------+---------+---------------------+------+
| LUCAS     | FERRIS  | 1984-04-17 07:04:27 |   21 |
| STAFFORD  | KIPP    | 1953-04-22 06:04:50 |   52 |
| GUTHREY   | HOLMES  | 1974-09-12 08:09:22 |   31 |
| TALIA     | KNOX    | 1966-08-14 11:08:14 |   39 |
| JOHN      | MORALES | 1956-06-22 07:06:14 |   49 |
+-----------+---------+---------------------+------+

When Things Go Wrong

Even if our programs have been thoroughly tested and have no bugs, user input can cause errors to occur in our code. The MySQL stored program language offers a powerful mechanism for handling errors. In Example 1-5, we create a procedure that creates new product codes or—if the product code already exists—updates it with a new name. The procedure detects an attempt to insert a duplicate value by using an exception handler. If the attempt to insert fails, the error is trapped and an UPDATE is issued in place of the INSERT. Without the exception handler, the stored program execution is stopped, and the exception is passed back unhandled to the calling program.

Example 1-5. Error handling in a stored program

1  CREATE PROCEDURE sp_product_code
2      (in_product_code VARCHAR(2),
3       in_product_name VARCHAR(30))
4
5  BEGIN
6
7    DECLARE l_dupkey_indicator INT DEFAULT 0;
8    DECLARE duplicate_key CONDITION FOR 1062;
9    DECLARE CONTINUE HANDLER FOR duplicate_key SET l_dupkey_indicator =1;
10
11   INSERT INTO product_codes (product_code, product_name)
12   VALUES (in_product_code, in_product_name);
13
14   IF l_dupkey_indicator THEN
15     UPDATE product_codes
16        SET product_name=in_product_name
17      WHERE product_code=in_product_code;
18   END IF;
19
20 END

Let’s take a more detailed look at the error-handling aspects of this code:

Line(s)	Explanation
1-4	This is the header of the stored procedure, accepting two IN parameters: product code and product name.
7	Declare a variable that we will use to detect the occurrence of a duplicate key violation. The variable is initialized with a value of 0 (false); subsequent code will ensure that it gets set to a value of 1 (true) only if a duplicate key violation takes place.
8	Define a named condition, duplicate_key, that is associated with MySQL error 1062. While this step is not strictly necessary, we recommend that you define such conditions to improve the readability of your code (you can now reference the error by name instead of by number).
9	Define an error handler that will trap the duplicate key error and then set the value of the variable l_dupkey_indicator to 1 (true) if a duplicate key violation is encountered anywhere in the subsequent code.
11-12	Insert a new product with the user-provided code and name.
14	Check the value of the l_dupkey_indicator variable. If it is still 0, then the INSERT was successful and we are done. If the value has been changed to 1 (true), we know that there has been a duplicate key violation. We then run the UPDATE statement in lines 15-17 to change the name of the product with the specified code.

Error handling is a critical aspect of writing robust, maintainable MySQL stored programs. Chapter 6 takes you on an extensive tour of the various error-handling mechanisms in MySQL stored programs.

Triggers

A trigger is a stored program that is automatically invoked in response to an event within the database. In the MySQL 5 implementation, triggers are invoked only in response to DML activity on a specific table. The trigger can automatically calculate derived or denormalized values. Example 1-6 shows a trigger that maintains such a derived value; whenever an employee salary is changed, the value of the contrib_401K column is automatically set to an appropriate value.

Example 1-6. Trigger to maintain a derived column value

1  CREATE TRIGGER employees_trg_bu
2       BEFORE UPDATE ON employees
3       FOR EACH ROW
4    BEGIN
5      IF NEW.salary <50000 THEN
6        SET NEW.contrib_401K=500;
7     ELSE
8       SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;
9     END IF;
10  END

The following table explains this fairly simple and short trigger:

Line(s)	Explanation
1	A trigger has a unique name. Typically, you will want to name the trigger so as to reveal its nature. For example, the "bu" in the trigger’s name indicates that this is a BEFORE UPDATE trigger.
2	Define the conditions that will cause the trigger to fire. In this case, the trigger code will execute prior to an UPDATE statement on the employees table.
3	FOR EACH ROW indicates that the trigger code will be executed once for each row being affected by the DML statement. This clause is mandatory in the current MySQL 5 trigger implementation.
4-10	This BEGIN-END block defines the code that will run when the trigger is fired.
5-9	Automatically populate the contrib_401K column in the employees table. If the new value for the salary column is less than 50000, the contrib._401K column will be set to 500. Otherwise, the value will be calculated as shown in line 8.

There is, of course, much more that can be said about the MySQL stored program language—which is why you have hundreds more pages of material to study in this book! These initial examples should, however, give you a good feel for the kind of code you will write with the stored program language, some of its most important syntactical elements, and the ease with which you can write—and read—the stored program language code.