MySQL Stored Procedure Programming

Our First Stored Procedure

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>

Creating the Procedure

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