O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required