Home Articles Portfolio References Contact
 

Articles » Stored Procedures 1

Written by: Michael Bookmark and Share

Overview

Stored procedures are a new addition to MySQL with the release of MySQL 5.  They have been around for a long time in other Database Management Systems (DBMS) but have not been available in MySQL until now.  However, the documentation provided on MySQL's website along with various others out there is very limited and less than adequate for anything more complicated than inserting a record into a database.  Therefore it is the mission of this article to give a solid introduction into the world of stored procedures in MySQL with more complicated examples and articles coming later.

For starters, what is a stored procedure?  Well, first you must understand the basics of how a server side scripting language interacts with the database.  When the script needs a few records from the database, it creates a query in SQL.  Then, once that's built, it sends it to the DBMS.  The DBMS receives it, compiles it, executes it and then sends the results back to the script.  A stored procedure is a query that is pre-compiled in the DBMS meaning that the script doesn't create the SQL statement nor does it send any SQL to the DBMS and the DBMS doesn't have to receive or compile it.  When the script calls a stored procedure, it may send some parameters, but that's it.  The DBMS already has the SQL pre-compiled and is able to instantly start pulling the results from the database.  For larger queries this can have huge increases in performance since less work is done in all areas to perform the query.

Great, now how do we use them in PHP?  Well let me start out with a simple example and then explain it below:

The Basics

sp.example_procedure.sql
[CODE]
DELIMITER $$
 
DROP PROCEDURE IF EXISTS database.example_procedure $$

CREATE PROCEDURE database.example_procedure( )

$$

DELIMITER ;
[/CODE]

The first thing we do is set the delimiter to $$.  By default in MySQL the delimiter is the semicolon.  If we leave it as this, then when we run this stored procedure, it will hit the first semi colon and think it's done with the function's code.  Therefore we change it to something else so we can have many statements in the stored procedure without any issues.

Next, we get rid of any previous version of this stored procedure.  If we jump straight to the CREATE command without first removing a pre-existing procedure, then we'll encounter an error.

After that we create the procedure.  The procedure name is example_procedure and it's in the database called database.  Next you'll see the $$ delimiter to close out this function.

Finally we revert the delimiter back to the semicolon.

And that's it in a nutshell.  Now that we have a skeleton stored procedure, we can do some database interactions.  In order to get this function into the database, type this at the command prompt:

[CODE]
$ mysql -u username -p password < /path/to/sp.example_procedure.sql
[/CODE]

Then to call the function, log into mysql and run the command:

[CODE]
$ mysql -u username -p password
> CALL database.example_procedure();
[/CODE]

Adding SQL to the Function

Adding SQL code to the function is completely straight forward, especially since we've changed the delimiter to something other than the semicolon.  For example, say you wanted to insert a record into the database, you would do the following:

sp.example_procedure.sql
[CODE]
DELIMITER $$

DROP PROCEDURE IF EXISTS database.example_procedure $$

CREATE PROCEDURE database.example_procedure( )

  INSERT INTO table_name
     SET name = 'John Doe';

$$

DELIMITER ;
[/CODE]

That's great and all but definitely not pratical. To really use this, we need to be able to specify which name we want to insert.  To do this, we need a parameter.

Parameters

Parameters are ways to pass information to and from the stored procedure.  Parameters can either be in or out, meaning they can either pass information to the function or send information out of the function and to the caller.  In this example, we're going to specify the name we want to insert.

sp.example_procedure.sql
[CODE]
DELIMITER $$

DROP PROCEDURE IF EXISTS database.example_procedure $$

CREATE PROCEDURE database.example_procedure( IN person_name VARCHAR(64) )

  INSERT INTO table_name
     SET name = person_name;

$$

DELIMITER ;
[/CODE]



MORE TO COME...CHECK BACK SOON!!