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.
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
SELECT statements directly into your code,
as shown in Example
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:
This section, the header of the
program, defines the name (
Here we declare an integer variable to hold the results of a database query that we will subsequently execute.
We run a query to determine the
total number of books that Guy has authored or coauthored.
Pay special attention to line 6: the
We use a simple
This single-line comment explains
the purpose of the
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
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:
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.
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:
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).
Declare a variable to hold the remaining balance for an account.
This simple loop (named so because
it is started simply with the keyword
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.
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:
Define the function: its name, input parameters (a single date), and return value (an integer).
Declare a local variable to hold the results of our age calculation.
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.
Otherwise (i.e., the birth date is yet to occur this year), we need to subtract an additional year from our age calculation.
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
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 | +-----------+---------+---------------------+------+
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.
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:
This is the header of the stored
procedure, accepting two
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.
Define a named condition,
Define an error handler that will
trap the duplicate key error and then set the value of the
Insert a new product with the user-provided code and name.
Check the value of the
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.
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.
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:
A trigger has a unique name.
Typically, you will want to name the trigger so as to reveal
its nature. For example, the "
Define the conditions that will
cause the trigger to fire. In this case, the trigger code
will execute prior to an
Automatically populate the
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.