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