MySQL Stored Procedure Programming

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When MySQL first emerged into the IT world in the mid-1990s, it had few of the characteristics normally associated with commercial relational databases. Features such as transactional support, subqueries, views, and stored procedures were conspicuously absent. Subsequent releases provided most of the missing features, and now—with the introduction of stored procedures, functions, and triggers in MySQL 5 (as well as updateable views and a data dictionary)—the feature gap between MySQL and other relational database systems is narrow indeed.

The introduction of stored programs (our generic term for stored procedures, functions, and triggers) has significance beyond simply winning a features war with competitive database systems. Without stored programs, MySQL cannot claim full compliance with a variety of standards, including ANSI/ISO standards that describe how a DBMS should execute stored programs. Furthermore, judicious use of stored programs can lead to greater database security and integrity and can improve overall application performance and maintainability. We outline these advantages in greater detail later in this chapter.

In short, stored programs—procedures, functions, and triggers—add significantly to the capabilities of MySQL, and a working knowledge of stored programming should be an essential skill for the MySQL professional.

This chapter introduces the MySQL stored program language, its origins, and its capabilities. It also offers a guide to additional resources for MySQL stored program developers and some words of overall development advice.

A database stored program—sometimes called a stored module or a stored routine—is a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server's system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A database stored program—sometimes called a stored module or a stored routine—is a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server's system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

There are three major types of MySQL stored programs:

Stored procedures: Stored procedures are the most common type of stored program. A stored procedure is a generic program unit that is executed on request and that can accept multiple input and output parameters.
Stored functions: Stored functions are similar to stored procedures , but their execution results in the return of a single value. Most importantly, a stored function can be used within a standard SQL statement, allowing the programmer to effectively extend the capabilities of the SQL language.
Triggers: Triggers are stored programs that are activated in response to, or are triggered by, an activity within the database. Typically, a trigger will be invoked in response to a DML operation (INSERT, UPDATE, DELETE) against a database table. Triggers can be used for data validation or for the automation of denormalization.

Other databases offer additional types of stored programs , including packages and classes, both of which allow you to define or collect multiple procedures and functions within a single, named context. MySQL does not currently support such structures—in MySQL, each stored program is a standalone entity.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The introduction of stored programs in MySQL 5 is a significant milestone in the evolution of the MySQL language. For any new technology to be absorbed and leveraged fully, users of that technology need lots of support and guidance in how best to utilize it. Our objective is to offer in this book complete and comprehensive coverage of the MySQL stored program language.

We are certain, however, that you will need help in other ways, so in the following sections we describe additional resources that either complement this book (by providing information about other MySQL technologies) or provide community-based support or late-breaking news. In these sections we provide quick summaries of many of these resources. By taking full advantage of these resources, many of which are available either free or at a relatively low cost, you will greatly improve the quality of your MySQL development experience—and your resulting code.

Over the years, the MySQL series from O'Reilly has grown to include quite a long list of books. Here we list some of the books currently available that we feel could be pertinent to the MySQL stored program developer, as well as relevant books from other publishers. Please check out the MySQL area of the O'Reilly OnLAMP web site (http://www.onlamp.com/onlamp/general/mysql.csp) for more complete information.

MySQL Stored Procedure Programming, by Guy Harrison with Steven Feuerstein: This is the book you are holding now (or maybe even viewing online). This book was designed to be a complete and comprehensive guide to the MySQL stored program language. However, this book does not attempt complete coverage of the MySQL server, the SQL language, or other programming languages that you might use with MySQL. Therefore, you might want to complement this book with one or more other topics from the O'Reilly catalog or even—heaven forbid—from another publisher!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

By definition, everyone is new to the world of MySQL stored program development, because stored programs are themselves new to MySQL. However, Guy and Steven have both had plenty of experience in stored program development within other relational databases. Steven, in particular, has been a key figure in the world of Oracle PL/SQL (Oracle's stored program language) development for more than a decade. We hope that you will find it helpful if we share some advice with you on how you can work more effectively with this powerful MySQL programming language.

We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let's get right to it—right?

Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don't respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.

We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:

Construct test cases and test scripts before you write your code: You should determine how you want to verify a successful implementation before you write a single line of a program. By doing this, you are more likely to get the interface of your program correct and be able to thoroughly identify what it is your program needs to do.
Establish clear rules for how developers will write the SQL statements in the application

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we took you on a whirlwind tour of the MySQL relational database and the new MySQL stored program language. We also provided you with some useful resources and added some general words of advice that we hope you find useful.

In the next chapter, we'll provide a more comprehensive tutorial that will really get you started with MySQL stored procedures, functions, and triggers.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL stored programming is a complex topic. We offer this chapter to introduce you to the main and common tasks you will need to perform, including:

How to create a stored program
How to pass information in and out of the stored program
How to interact with the database
How to create procedures, functions, and triggers in the MySQL stored program language

We don't go into detail in this chapter. Our purpose is to get you started and to give you some appreciation of how stored programs work. Later chapters will explore in detail all of the topics touched on in this chapter.

To follow along with the examples in this tutorial , you will need:

A MySQL 5 server
A text editor such as vi, emacs, or Notepad
The MySQL Query Browser

You can get the MySQL server and MySQL Query Browser from http://dev.mysql.com.

We'll start by creating a very simple stored procedure. To do this, you need an editing environment in which to write the stored procedure and a tool that can submit the stored procedure code to the MySQL server.

You can use just about any editor to write your code. Options for compiling that code into MySQL include:

The MySQL command-line client
The MySQL Query Browser
A third-party tool such as Toad for MySQL

In this chapter, we won't make any assumptions about what tools you have installed, so we'll start with the good old MySQL command-line client.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To follow along with the examples in this tutorial , you will need:

A MySQL 5 server
A text editor such as vi, emacs, or Notepad
The MySQL Query Browser

You can get the MySQL server and MySQL Query Browser from http://dev.mysql.com.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We'll start by creating a very simple stored procedure. To do this, you need an editing environment in which to write the stored procedure and a tool that can submit the stored procedure code to the MySQL server.

You can use just about any editor to write your code. Options for compiling that code into MySQL include:

The MySQL command-line client
The MySQL Query Browser
A third-party tool such as Toad for MySQL

In this chapter, we won't make any assumptions about what tools you have installed, so we'll start with the good old MySQL command-line client.

Let's connect to the MySQL server on the local host at port 3306 using the root account. We'll use the preinstalled "test" database in Example 2-1.

Example 2-1. Connecting to the MySQL command-line client

[gharriso@guyh-rh4-vm2 ~]$mysql -uroot -psecret -hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nightly-20051017-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can create a stored program with the CREATE PROCEDURE , CREATE FUNCTION , or CREATE TRIGGER statement. It is possible to enter these statements directly at the MySQL command line, but this is not practical for stored programs of more than trivial length, so the best thing for us to do is to create a text file containing our stored program text. Then we can submit this file to the database using the command-line client or another tool.

We will use the MySQL Query Browser as a text editor in this example. If you don't have this tool, you can download it from http://dev.mysql.com/downloads/. Alternately, you could use an OS text editor such as vi, emacs, or Notepad. We like the MySQL Query Browser because of its built-in help system, syntax highlighting, ability to run SQL statements, and lots of other features.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Local variables can be declared within stored procedures using the DECLARE statement. Variable names follow the same naming rules as MySQL table column names and can be of any MySQL data type. You can give variables an initial value with the DEFAULT clause and assign them new values using the SET command, as shown in Figure 2-5.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most of the stored programs you write will include one or more parameters. Parameters make stored programs much more flexible and therefore more useful. Next, let's create a stored procedure that accepts parameters.

Figure 2-4: Executing the stored procedure in the Query Browser

The stored procedure shown in Figure 2-6 accepts an integer parameter, input_number, and calculates the square root of that number. The resulting number is returned as a result set.

Place parameters within parentheses that are located immediately after the name of the stored procedure. Each parameter has a name, a data type, and, optionally, a mode. Valid modes are IN (read-only), INOUT (read-write), and OUT (write-only). No parameter mode appears in Figure 2-6, because IN is the default and this is an IN parameter.

We'll take a closer look at parameter modes following this example.

In addition to the parameter, this stored procedure introduces two other features of MySQL stored programs:

DECLARE: A statement used to create local variables for use in the stored program. In this case, we create a floating-point number called l_sqrt.

Figure 2-5: Examples of variables in stored procedures

SET: A statement used to assign a value to a variable. In this case, we assign the square root of our input parameter (using the built-in SQRT function) to the floating-point number we created with the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can control the flow of execution in your stored program by using IF or CASE statements. Both have roughly the same functionality; we will demonstrate the use of IF in this tutorial, as it's probably the most familiar of the two constructs.

Figure 2-8 shows a stored program that works out the discounted rate for a purchase based on the size of the purchase, and Example 2-5 shows its execution. Purchases over $500 get a 20% discount, while purchases over $100 get a 10% discount.

Figure 2-8: Conditional execution with the IF statement

Example 2-5. Creating and executing a stored procedure that contains an IF statement

mysql> SOURCEdiscounted_price.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLdiscounted_price(300,@new_price) $$
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT@new_price$$
+------------+
| @new_price |
+------------+
| 270.0      |
+------------+
1 row in set (0.00 sec)

The IF statement allows you to test the truth of an expression such as normal_price > 500 and take appropriate action based on the result of the expression. As with other programming languages, the ELSEIF clause is used for all conditional branches after the initial IF. The ELSE clause is executed if the Boolean expressions in the IF and ELSEIF clauses all evaluate to false.

CASE has very similar functionality, and may be preferable when you are comparing a single expression against a set of possible distinct values. The two conditional statements are explored and contrasted in Chapter 4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Loops allow stored programs to execute statements repetitively. The MySQL stored program language offers three types of loops :

Simple loops using the LOOP and END LOOP clauses
Loops that continue while a condition is true, using the WHILE and END WHILE clauses
Loops that continue until a condition is true, using the REPEAT and UNTIL clauses

With all three loop types, you terminate execution of the loop with the LEAVE statement.

All three types of loops are described in detail in Chapter 4; we'll only demonstrate the LOOP-LEAVE-END LOOP (simple loop) sequence in this tutorial.

Figure 2-9 shows a very simple loop.

Figure 2-9: A simple loop inside a stored procedure

Here is an explanation of the activity in this stored procedure:

Line(s)	Explanation
7	Declare a simple numeric variable called `counter` with an initial value of 0.
9-14	The simple loop. All statements between `LOOP` and `END LOOP` are repeated until a `LEAVE` clause is executed.
9	The `LOOP` statement is prefixed by the `my_simple_loop:` label. The `LEAVE` statement requires that the loop be labeled so it knows which loop to exit.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When an error occurs in a stored program, the default behavior of MySQL is to terminate execution of the program and pass the error out to the calling program. If you need a different kind of response to an error, you create an error handler that defines the way in which the stored program should respond to one or more error conditions.

The following are two relatively common scenarios that call for the definition of error handlers:

If you think that an embedded SQL statement might return no rows, or you need to fetch all the rows from a SELECT statement using a cursor, a NOT FOUND error handler will prevent the stored program from terminating prematurely.
If you think that a SQL statement might return an error (a constraint violation, for instance), you may need to create a handler to prevent program termination. The handler will, instead, allow you to process the error and continue program execution.

Chapter 6 describes in detail how to use error handlers. An example of using a NOT FOUND error handler with a cursor is shown in the next section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most stored programs involve some kind of interaction with database tables. There are four main types of interactions:

Store the results of a SQL statement that returns a single row into local variables.
Create a "cursor" that allows the stored program to iterate through the rows returned by a SQL statement.
Execute a SQL statement, returning the result set(s) to the calling program.
Embed a SQL statement that does not return a result set, such as INSERT, UPDATE, DELETE, etc.

The following sections look briefly at each type of interaction.

To run the examples in this section of the chapter, you should install the book's sample database, available at this book's web site (see the Preface for details).

Use the SELECT INTO syntax when you are querying information from a single row of data (whether retrieved from a single row, an aggregate of many rows, or a join of multiple tables). In this case, you include an INTO clause "inside" the SELECT statement that tells MySQL where to put the data retrieved by the query.

Figure 2-10 shows a stored procedure that obtains and then displays the total sales for the specified customer ID. Figure 2-6 executes the procedure.

Figure 2-10: A stored procedure with an embedded SELECT INTO statement

Example 2-6. Executing a stored procedure that includes a SELECT INTO statement

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Calling one stored program from another is perfectly simple. You do this with the CALL statement, just as you would from the MySQL command-line client.

Figure 2-15 shows a simple stored procedure that chooses between two stored procedures based on an input parameter. The output of the stored procedure (l_bonus_amount is populated from an OUT parameter) is passed to a third procedure.

Here is an explanation of the significant lines:

Line(s)	Explanation
11	Determine if the employee is a manager. If he is a manager, we call the `calc_manager_bonus` stored procedure; if he is not a manager, we call the `calc_minion_bonus` stored procedure.
12 and 14	With both stored procedures, pass in the `employee_id` and provide a variable—`l_bonus_amount`—to receive the output of the stored procedure.
16	Call the `grant_bonus` stored procedure that passes as arguments the `employee_id` and the bonus amount, as calculated by the stored procedure we called in line 12 or 14.

Figure 2-15: Example of calling one stored procedure from another

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Example 2-7 we show a stored procedure that uses all the features of the stored program language we have covered so far in this tutorial.

Example 2-7. A more complex stored procedure

1  CREATE PROCEDURE putting_it_all_together(in_department_id INT)
2      MODIFIES SQL DATA
3  BEGIN
4      DECLARE l_employee_id INT;
5      DECLARE l_salary      NUMERIC(8,2);
6     DECLARE l_department_id INT;
7     DECLARE l_new_salary  NUMERIC(8,2);
8     DECLARE done          INT DEFAULT 0;
9
10    DECLARE cur1 CURSOR FOR
11             SELECT employee_id, salary, department_id
12               FROM employees
13              WHERE department_id=in_department_id;
14
15
16          DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
17
18     CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
19       (employee_id INT, department_id INT, new_salary NUMERIC(8,2));
20
21     OPEN cur1;
22     emp_loop: LOOP
23
24       FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
25
26       IF done=1 THEN       /* No more rows*/
27          LEAVE emp_loop;
28       END IF;
29
30       CALL new_salary(l_employee_id,l_new_salary); /*get new salary*/
31
32       IF (l_new_salary<>l_salary) THEN             /*Salary changed*/
33
34          UPDATE employees
35             SET salary=l_new_salary
36           WHERE employee_id=l_employee_id;
37          /* Keep track of changed salaries*/
38          INSERT INTO emp_raises (employee_id,department_id,new_salary)
39           VALUES (l_employee_id,l_department_id,l_new_salary);
40       END IF;
41
42     END LOOP emp_loop;
43     CLOSE cur1;
44     /* Print out the changed salaries*/
45     SELECT employee_id,department_id,new_salary from emp_raises
46      ORDER BY employee_id;
47  END;

This is the most complex procedure we have written so far, so let's go through it line by line:

Line(s)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Stored functions are similar to stored procedures: they are named program units that contain one or more MySQL statements. They differ from procedures in the following ways:

The parameter list of a function may contain only IN parameters. OUT and INOUT parameters are not allowed. Specifying the IN keyword is neither required nor allowed.
The function itself must return a single value, whose type is defined in the header of the function.
Functions can be called from within SQL statements.
A function may not return a result set.

Generally, you should consider using a stored function rather than a stored procedure when you have a program whose sole purpose is to compute and return a single value or when you want to create a user-defined function for use within SQL statements.

Figure 2-16 shows a function that implements the same functionality found in the discount_price stored procedure we created earlier in this chapter.

Figure 2-16: A stored function

The following table explains a few things that set apart this function from its stored procedure equivalent:

Line	Explanation
7	Specify a `RETURNS` clause as part of the function definition. This specifies the type of data that the function will return.
8	MySQL applies stricter rules to stored functions than it does to procedures. A function must either be declared not to modify SQL (using the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A trigger is a special type of stored program that fires when a table is modified by an INSERT, UPDATE, or DELETE (DML) statement. Triggers implement functionality that must take place whenever a certain change occurs to the table. Because triggers are attached directly to the table, application code cannot bypass database triggers.

Typical uses of triggers include the implementation of critical business logic, the denormalization of data for performance reasons, and the auditing of changes made to a table. Triggers can be defined to fire before or after a specific DML statement executes.

In Figure 2-17, we create a trigger that fires before any INSERT statement completes against the sales table. It automatically applies free shipping and discounts to orders of a specified value.

Figure 2-17: A database trigger

Here is an explanation of the trigger definition:

Line(s)	Explanation
5	Specify the trigger name.
6	Specify that the trigger fires before an insert on the `sales` table.
7	Include the (currently) mandatory `FOR EACH ROW` clause, indicating that the statements within the trigger will be executed once for every row inserted into the `sales` table.
8	Use `BEGIN` to start the block containing statements to be executed by the trigger.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We've shown you how to call stored programs from the MySQL command-line client, from the MySQL Query Browser, and from another stored program. In the real world, however, you are more likely to call a stored program from another programming environment, such as PHP, Java, Perl, Python, or .NET. We discuss the details of using stored programs within each of these environments in Chapters Chapter 12 through Chapter 17.

For now, let's look at how you can call a stored procedure (shown in Figure 2-18) from PHP, which is probably the development environment most commonly used in conjunction with MySQL.

When interacting with MySQL from PHP, we can choose between the database-independent PEAR::DB extension, the mysqli (MySQL "improved") extension, and the more recent PHP Data Objects (PDO) extension. In this example we will use the mysqli extension. Chapter 13 describes the details of these extensions.

Figure 2-19 shows PHP code that connects to the MySQL server and calls the stored procedure. We won't step through the code here, but we hope that it will give you a sense of how stored programs can be used in web and other applications.

Figure 2-18: Stored procedure to be called from PHP

The PHP program prompts the user to specify a department ID; it then calls the stored procedure employee_list to retrieve a list of employees that belong to that department. Figure 2-20 shows the output displayed by the PHP/stored procedure example.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter we presented a brief "getting started" tutorial that introduced you to the basics of MySQL stored programs. We showed you how to:

Create a simple "Hello World" stored procedure.
Define local variables and procedure parameters.
Perform conditional execution with the IF statement.
Perform iterative processing with simple loops.
Include SQL statements inside stored procedures, including how to perform row-at-a-time processing with cursors.
Call a stored program from another stored program.
Create a stored function (and differentiate stored functions from stored procedures).
Create a trigger on a table to automate denormalization.
Call a stored procedure from PHP.

Figure 2-19: Sample PHP program calling a stored procedure

You may now be tempted to put down this book and start writing MySQL stored programs. If so, we congratulate you on your enthusiasm. May we suggest, however, that you first spend some time reading more detailed explanations of each of these areas of functionality in the following chapters? That way, you are likely to make fewer mistakes and write higher-quality code.

Figure 2-20: Output from our PHP example

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduces the MySQL stored program language, a simple, readable but complete programming language based on the ANSI SQL:2003 SQL/PSM (Persistent Stored Module) specification.

The MySQL stored program language is a block-structured language (like Pascal) that includes familiar statements for manipulating variables , implementing conditional execution, performing iterative processing, and handling errors. Users of other stored program languages such as Oracle PL/SQL or Microsoft SQL Server Transact-SQL will find features of the language very familiar. In fact, users of the IBM DB2 SQL Procedural language will find MySQL's stored program language almost identical—both are based on the SQL/PSM specification. Users of other programming languages that are typically used with MySQL—such as PHP, Java, or Perl—might find the stored program language a little verbose, but should have no difficulty at all learning the language.

In this chapter we will look at the fundamental building blocks of the stored program language—variables, literals, parameters, comments, operators, expressions, and data types. We will also discuss MySQL 5 "strict" mode and its implications. In the next chapter we will build on this base by describing the block structure, conditional statements (IF and CASE), and looping capabilities of the language.

Let's start with a review of how we define and use various data items—variables, literals, and parameters—in our stored programs and how we can add comments to document our code.

The first thing we'll look at is how the MySQL stored program language deals with variables and literals, because without some understanding of these items, we can't create any meaningful examples for any other topics.

A variable is a named data item whose value can change during program execution. A literal (described in the next section) is an unnamed data item that can be assigned to a variable. Typically, literals are hardcoded into your stored program code and are usually assigned to variables , passed as parameters, or used as arguments to

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Let's start with a review of how we define and use various data items—variables, literals, and parameters—in our stored programs and how we can add comments to document our code.

The first thing we'll look at is how the MySQL stored program language deals with variables and literals, because without some understanding of these items, we can't create any meaningful examples for any other topics.

A variable is a named data item whose value can change during program execution. A literal (described in the next section) is an unnamed data item that can be assigned to a variable. Typically, literals are hardcoded into your stored program code and are usually assigned to variables , passed as parameters, or used as arguments to SELECT statements.

The DECLARE statement allows us to create a variable. As we will see a bit later on, it appears within a block of code before any cursor or handler declarations and before any procedural statements. The syntax of the DECLARE statement is:

    DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

Multiple variables may be declared in a single DECLARE statement, and the variable(s) can be assigned a default (or initial) value. If you don't use the DEFAULT clause, then the variable starts off with the NULL value.

Using DEFAULT is a good practice because, unless you initialize a variable, any subsequent operations on that variable—other than a simple assignment—may also return NULL. We'll give an example of this type of error later in the chapter.

The datatype may be any of the valid MySQL data types that you can use in a CREATE TABLE statement. We provide detailed descriptions of each data type later in this chapter; Table 3-1 summarizes those most commonly used.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL operators include the familiar operators common to most programming languages, although C-style operators (++,—,+=, etc.) are not supported.

Operators are typically used within the SET statement to change the value of a variable, within comparison statements such as IF or CASE, and in loop control expressions. Example 3-10 shows a few simple examples of using operators within stored programs.

Example 3-10. Examples of operators in a stored program

create procedure operators(  )
begin
        DECLARE a int default 2;
        declare b int default 3;
        declare c FLOAT;

        set c=a+b; select 'a+b=',c;
        SET c=a/b; select 'a/b=',c;
        SET c=a*b; Select 'a*b=',c;


            IF (a<b) THEN
                select 'a is less than b';
        END IF;
        IF NOT (a=b) THEN
                SELECT 'a is not equal to b';
        END IF;
end;

The various types of operators (mathematical , comparison , logical, and bitwise) are described in the following subsections.

MySQL supports the basic mathematical operators you learned about in elementary school (pay attention class!): addition (+), subtraction (-), multiplication (*), and division (/).

In addition, MySQL supports two additional operators related to division: the DIV operator returns only the integer portion of division, while the modulus operator (%) returns only the remainder from a division. Table 3-2 lists, describes, and provides an example of the MySQL mathematical operators.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|

Table of Contents