Stored Procedures in MySQL 5.0
Posted by: Adam Plocher on March 20, 2006
WHAT IS A STORED PROCEDURE?
A stored procedure is a program that runs directly on the database server. Stored procedures are written with SQL and can be used to improve performance and help with ease of development.
WHY USE STORED PROCEDURES?
Stored procedures can be useful on many different levels. If you have a script that gets called often or uses any looped queries you may be generating a lot more network traffic than you should be. Stored procedures will cut down on long queries being sent over the network by turning a potentially long query into a short alias.
Another thing stored procedures can help with is the speed of execution. Generally the more data manipulation that's done directly against the database is faster than expecting your application to handle it. Using complex stored procedures could save you a lot of time when executing a query.
By using stored procedures you're adding an extra data-layer, so to speak. This means you may be able to fix problems in your application by simply editing a stored procedure, instead of having the change your application code. In many cases, this is much more convenient.
BEFORE WE START
It would be a good idea for you to grab the latest version of MySQL Query Browser, if you haven't already. This will make creating stored procedures WAY easier.
For the samples in this guide I'm using a table called "News" with an auto-incrementing integer field called NewsID and a varchar(32) field called Title. The database is called "test".
CREATING STORED PROCEDURES
I would suggest first downloading the latest version of MySQL Query Browser. Once you have that installed and setup to connect to your MySQL 5.0 database, you should be able to easily create a stored procedure stub by going to the "Script" menu and "Create Stored Procedure/Function". Give it a descriptive name (but not too long) and click "Create PROCEDURE".
If you don't have MySQL Query Browser, this is what it would generate for you. You should be able to use this in any query tool for MySQL 5.0.
If you execute this query, it will successfully create a stored procedure, but it won't do anything yet. Let's analyze what this CREATE PROCEDURE query is doing:
The "DELIMITER $$" statement causes MySQL to use two dollar signs ($$) as it's end-of-line delimiter, instead of a semi-colon (;). This will allow any SQL between the BEGIN and END to have a semi-colon at the end of the line, without causing MySQL to think the query is done. The END statement needs the two dollar signs after it so MySQL knows the query is done and ready to be executed.
If the stored procedure already exists, MySQL will get rid of it before re-creating it, by calling the "DROP PROCEDURE IF EXISTS" statement. "sprocTest" is the name of your stored procedure.
Now let's make our original stored procedure do something and pass in a parameter.
Once you have created this stored procedure, you can execute it by using the CALL statement in MySQL:
The following procedure will update a record if it exists, otherwise insert a new one. It utilizes the IF, ELSE, SET and EXISTS statements.
To execute this procedure we need to call:
GETTING A LIST OF YOUR SPROCS
If you're not using the MySQL Query Browser, you might not have the luxury of having a list of stored procedures presented to you at all times. To manually get a list from MySQL use the following query:
or to search for a stored procedure:
So that will give you a list of stored procedures, but what if you want to see the actual code behind that stored procedure? That's where the SHOW CREATE PROCEDURE query comes in:
REFERENCES
MySQL Stored Procedures
RELATED ITEMS
Triggers in MySQL 5.0
Digg It
A stored procedure is a program that runs directly on the database server. Stored procedures are written with SQL and can be used to improve performance and help with ease of development.
WHY USE STORED PROCEDURES?
Stored procedures can be useful on many different levels. If you have a script that gets called often or uses any looped queries you may be generating a lot more network traffic than you should be. Stored procedures will cut down on long queries being sent over the network by turning a potentially long query into a short alias.
Another thing stored procedures can help with is the speed of execution. Generally the more data manipulation that's done directly against the database is faster than expecting your application to handle it. Using complex stored procedures could save you a lot of time when executing a query.
By using stored procedures you're adding an extra data-layer, so to speak. This means you may be able to fix problems in your application by simply editing a stored procedure, instead of having the change your application code. In many cases, this is much more convenient.
BEFORE WE START
It would be a good idea for you to grab the latest version of MySQL Query Browser, if you haven't already. This will make creating stored procedures WAY easier.
For the samples in this guide I'm using a table called "News" with an auto-incrementing integer field called NewsID and a varchar(32) field called Title. The database is called "test".
-- create News table, be sure to be in a 'test' DB
CREATE TABLE News
(NewsID int auto_increment not null, Title varchar(32), primary key(NewsID))
CREATING STORED PROCEDURES
I would suggest first downloading the latest version of MySQL Query Browser. Once you have that installed and setup to connect to your MySQL 5.0 database, you should be able to easily create a stored procedure stub by going to the "Script" menu and "Create Stored Procedure/Function". Give it a descriptive name (but not too long) and click "Create PROCEDURE".
If you don't have MySQL Query Browser, this is what it would generate for you. You should be able to use this in any query tool for MySQL 5.0.
DELIMITER $$
DROP PROCEDURE IF EXISTS sprocTest $$
CREATE PROCEDURE sprocTest ()
BEGIN
END $$
DELIMITER ;
If you execute this query, it will successfully create a stored procedure, but it won't do anything yet. Let's analyze what this CREATE PROCEDURE query is doing:
The "DELIMITER $$" statement causes MySQL to use two dollar signs ($$) as it's end-of-line delimiter, instead of a semi-colon (;). This will allow any SQL between the BEGIN and END to have a semi-colon at the end of the line, without causing MySQL to think the query is done. The END statement needs the two dollar signs after it so MySQL knows the query is done and ready to be executed.
If the stored procedure already exists, MySQL will get rid of it before re-creating it, by calling the "DROP PROCEDURE IF EXISTS" statement. "sprocTest" is the name of your stored procedure.
Now let's make our original stored procedure do something and pass in a parameter.
DELIMITER $$
DROP PROCEDURE IF EXISTS sprocTest $$
CREATE PROCEDURE sprocTest (id int)
BEGIN
SELECT * FROM News WHERE NewsID=id;
END $$
DELIMITER ;
Once you have created this stored procedure, you can execute it by using the CALL statement in MySQL:
-- change the 1 if you want to pull another id
-- it corresponds to the id parameter
CALL sprocTest(1)
The following procedure will update a record if it exists, otherwise insert a new one. It utilizes the IF, ELSE, SET and EXISTS statements.
DELIMITER $$
DROP PROCEDURE IF EXISTS sprocTest $$
CREATE PROCEDURE sprocTest (id int, title varchar(32))
BEGIN
-- INSERT NEW RECORD IF PREEXISTING RECORD DOESNT EXIST
IF (id = 0) THEN
SET id = null;
END IF;
IF (id IS NOT NULL) AND (EXISTS (SELECT * FROM News WHERE NewsID=id))
THEN
UPDATE News SET Title=title WHERE NewsID=id;
ELSE
INSERT INTO News (Title) VALUES (title);
END IF;
END $$
DELIMITER ;
To execute this procedure we need to call:
-- this will update recordID 1
CALL sprocTest(1,'Some News Title');
-- this will create a new record
CALL sprocTest(null, 'Some News Title');
GETTING A LIST OF YOUR SPROCS
If you're not using the MySQL Query Browser, you might not have the luxury of having a list of stored procedures presented to you at all times. To manually get a list from MySQL use the following query:
SHOW PROCEDURE STATUS;
or to search for a stored procedure:
SHOW PROCEDURE STATUS LIKE '%Test%';
So that will give you a list of stored procedures, but what if you want to see the actual code behind that stored procedure? That's where the SHOW CREATE PROCEDURE query comes in:
SHOW CREATE PROCEDURE sprocTest;
REFERENCES
MySQL Stored Procedures
RELATED ITEMS
Triggers in MySQL 5.0
Digg It
Comments temporarily disabled
