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 ...