:: DEVELOPER ZONE
MySQL Manual | 20 Stored Procedures and Functions
Search the MySQL manual:
- 1 General Information
- 2 Installing MySQL
- 3 MySQL Tutorial
- 4 Using MySQL Programs
- 5 Database Administration
- 6 Replication in MySQL
- 7 MySQL Optimization
- 8 MySQL Client and Utility Programs
- 9 MySQL Language Reference
- 10 Language Structure
- 11 Character Set Support
- 12 Column Types
- 13 Functions and Operators
- 14 SQL Statement Syntax
- 15 MySQL Storage Engines and Table Types
- 16 The
InnoDB
Storage Engine - 17 MySQL Cluster
- 18 Introduction to MaxDB
- 19 Spatial Extensions in MySQL
- 20 Stored Procedures and Functions
- 21 MySQL APIs
- 22 Error Handling in MySQL
- 23 Extending MySQL
- A Problems and Common Errors
- B Credits
- C MySQL Change History
- D Porting to Other Systems
- E Environment Variables
- F MySQL Regular Expressions
- G GNU General Public License
- H MySQL FLOSS License Exception
- SQL Command, Type, and Function Index
- Concept Index
Additional languages
Additional formats
20 Stored Procedures and Functions
Stored procedures and functions are a new feature in MySQL version 5.0. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.
Some situations where stored procedures can be particularly useful:
- When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
- When security is paramount. Banks, for example, use stored procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific stored procedures.
Stored procedures can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server system because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.
Stored procedures also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally, for example, by using classes. Using these client application language features is beneficial for the programmer even outside the scope of database use.
MySQL follows the SQL:2003 syntax for stored procedures, which is also used by IBM's DB2.
The MySQL implementation of stored procedures is still in progress. All syntax described in this chapter is supported and any limitations and extensions are documented where appropriate.
Stored procedures require the proc
table in the mysql
database. This table is created during the MySQL 5.0 installation procedure. If you are upgrading to MySQL 5.0 from an earlier version, be sure to update your grant tables to make sure that the proc
table exists. See section 2.5.8 Upgrading the Grant Tables.
Subsections
- 20.1 Stored Procedure Syntax
© 1995-2004 MySQL AB. All rights reserved.
User Comments
You CAN return a result set from a procedure. I was under the impression that it was not possible because the documentation is a bit, well... lacking in useful examples. It does not say anything about returning a table as your result of the procedure, at least I didnt see that, maybe I missed it. Frankly, if it didnt return a result set, I would have no use for stored procedures.
But it does indeed! :) Here's what I did:
===========================
Delimiter $
create procedure sp_SortPlayerList(
IN SPT varchar(30),
IN TY INT,
IN OB varchar(20)
)
BEGIN
If OB = 'LastName' then
select * from tsdata.Players
where TourneyYear = TY
and Sport = SPT
Order by LastName;
else
select * from tsdata.Players;
end if;
END$
call sp_SortPlayerList('Volleyball', 2004, 'LastName')$
============================
You can see in the 'call sp...' line, that it is not followed by a 'select @parameter' satement (as it does in the documentation), you just call it and it returns the records.
Also, you do not use parameters within your procedure using an '@'. In the example above, you do not do: 'If @OB = 'LastName' then'. Leave the '@' off of it. This one really messed me up.
I hope this helps someone out there, I just spent hours trying to figure this out.
Good luck!
I believe that the current MySQL online poll asking for the features that developers such as us are most looking forward to is very enlightening to this topic. At present, the number 1 feature is Stored Procedures. I have seen the range of comments both here and in other forums equating SP's to both bad and good practice. I began my SQL by avoiding SPs entirely as I had read they were somehow bad practice. As my SQL skills improved, I began experimenting with Stored Procedures and found that, contrary to the naysayers, SP's are exceptionally valuable. I have slowly and steadily converted the vast majority of the Project Management database that I wrote and administer to using SP's for most data transactions. This has paid off by reducing code and unifying the methods used to update and insert data across the entire application. I found that I had coded certain data transactions in different manners in different areas of the application even though they should have been the same. SP's allowed me to unify this. The other huge advantage is, of course, precompiling the SP (SQL Server 2000) gives huge speed advantages. The T-SQL language gives me the ability to call an SP from the application, and then have the SP execute many various queries and updates contained within transactions that do lock checks and deadlock handling. I have many 10 page SP's that run huge amounts of data transactions and exit in milliseconds. I also appreciate the fact that SP's run on the server, not the client and reduce network traffic. This has given my application huge speed increases and reduced our network traffic to almost nothing. I am very much looking forward to complete implementation of Stored Procedures in MySQL and think that I will likely port the entire application out of MS SQL Server 2000 as soon as this is done. The only other features I need to be able to do this are Jobs that can be run by the server on schedules and MySQL also needs to improve Transactions.
In regards to other comments about using SP's to use cursors to process data... I HIGHLY discourage the use of cursors. To quote another SQL professional that I sadly foget his name "Cursors are Evil". The fact is that you can probably rewrite nearly any task using a cursor into a SQL statement (or set of SQL statements). Cursors run extremely slooooow compared to standard ANSI SQL statements. When my SQL skills were quite novice, I used a SP to run a cursor to convert huge blocks of data and import them into new tables. The procedure took just over 2 hours to process 11GB of data. When my SQL skills improved, I rewrote that SP, removing the cursor and replacing it with a complex, nested, pure SQL statement. The new SP ran in 5 minutes and did the entire 11GB data processing. Cursors should be avoided at all costs in a production environment. In lieu of using a cursor, I recommend you learn more about SQL.
The prior comment about the usefulness of stored procedures is TRUE; the comment about avoiding cursors at all costs is FALSE.
One must always use the appropriate tool in its most advantageous context. One bad programming choice does not prove that the tool is bad.
For example, it is possible for a compiler to prepare the SQL in a declared CURSOR in advance, so that repeated fetches of the cursor do not require repeated prepares of the same SQL. This can avoid a huge amount of server traffic, especially for one-row result sets of primary key look-ups.
We don't yet know how MySQL compiles cursors, but Oracle cursors can be many times FASTER in the example i just gave.
There are too few examples about stored procedures up to now. I post a simple example here and I hope it is useful to beginners of MySQL like me :)
create table catagory
(
catagory_id int unsigned not null auto_increment,
name varchar(50) not null,
description text,
primary key (catagory_id)
) type=innodb;
create table catagory_set
(
master_id int unsigned not null,
slave_id int unsigned not null,
index(master_id),
index(slave_id),
primary key (master_id,slave_id),
foreign key (master_id) references catagory (catagory_id) on delete cascade,
foreign key (slave_id) references catagory (catagory_id) on delete cascade
) type=innodb;
drop procedure add_catagory;
delimiter ?
create procedure add_catagory (IN param1 int, IN param2 char(50),
IN param3 text, OUT cid int, OUT error_msg char(80))
begin
declare master_id, master_exist, name_exist int;
set cid = -1;
set name_exist = 0, master_exist = 0;
# Insert a subcatagory #
if param1 > 0 then
# Check if the master catagory ID is valid #
select count(catagory_id), catagory_id into master_exist, master_id
from catagory where catagory_id=param1 group by catagory_id;
# Check if the same catagory name exist and the master catagory #
select count(catagory_id) into name_exist from catagory, catagory_set
where catagory.name=param2 and catagory.catagory_id=catagory_set.slave_id
and catagory_set.master_id=master_id;
if master_exist > 0 and name_exist = 0 then
lock tables catagory write, catagory_set write;
flush table catagory, catagory_set;
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
insert into catagory_set values (param1, cid);
unlock tables;
elseif master_exist = 0 then
set error_msg = 'The master catagory ID provided does not exist';
elseif name_exist > 0 then
set error_msg = 'The catagory name already exist, please choose another name';
end if;
# Insert a primary catagory #
else
# Search and compare the name of all primary catagory #
select count(catagory_id) into name_exist from catagory
where name = param2 and not exists(
select * from catagory_set
where catagory_set.slave_id = catagory.catagory_id
);
if name_exist > 0 then
set error_msg = 'The catagory name already exist, please choose another name';
else
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
end if;
end if;
end ?
delimiter ;
call add_catagory(1,'Planet','Earth',@cid,@error);
select @cid, @error;
Add your own comment.