:: Developer Zone
Login / register
MySQL.com Developer Zone Partner Solutions Online Shop
Downloads Documentation Developer Support TECH Resources Books
Mysql Manual | 20 Stored Procedures and Functions
Overview MySQL Reference Manual Maxdb Documentation Connectors
Search the mysql manual:
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
20.1 Stored Procedure Syntax 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
Get The MySQL Language Reference and MySQL Administrator's Guide from MySQL PRESS!
Additional Languages
French portuguese
Additional Formats
PDF (A4) PDF (US Letter) HTML, One Page Per Chapter, Tarball HTML, One Page Page, Zip HTML, All On ONE Page, Tarball Html
Previous
/
NEXT
/
UP
/
Table of contents
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 do not 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 .
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 ....................................
Subsections
20.1 Stored Procedure Syntax
20.1.1 Maintaining Stored Procedures
20.1.1.1 CREATE PROCEDURE and CREATE FUNCTION 20.1.1.2 ALTER PROCEDURE and ALTER FUNCTION 20.1.1.3 DROP PROCEDURE and DROP FUNCTION 20.1.1.4 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION 20.1.2 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS 20.1.3 CALL Statement 20.1 .4 Begin ... End Compound Statement 20.1.5 Declare Statement 20.1.6 Variables in Stored Procedures
20.1.6.1 Declare Local Variables 20.1.6.2 Variable Set Statement 20.1.6.3 Select ... INTO Statement 20.1.7 Conditions and Handlers
20.1.7.1 Declare Conditions 20.1.7.2 Declare Handlers 20.1.8 Cursors
20.1.8.1 Declaring Cursors 20.1.8.2 Cursor Open Statement 20.1.8.3 Cursor Fetch Statement 20.1.8.4 Cursor Close Statement 20.1.9 Flow Control Construction
20.1.9.1 IF Statement 20.1.9.2 Case Statement 20.1.9.3 Loop Statement 20.1.9.5 Leave Statement 20.1.9.5 Itete Statement 20.1.9.6 Repeat Statement 20.1.9.7 While Statement
Previous
/
NEXT
/
UP
/
Table of contents
User Comments
Posted by [name withheld] on February 7 2004 7:00 PM [delete] [edit]
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, 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 rour (20) beginif ob = ' LastName 'thenselect * from tsdata.Playerswhere TourneyYear = TYand Sport = SPTOrder by LastName; elseselect * from tsdata.Players; end if; END $ call sp_SortPlayerList (' Volleyball ', 2004,' LastName ') $ ======= ===================== You can see in 'Call sp ...' line, That it is not followed by a 'select @Parameter' Satement 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 'TEN'. Leave the '@' Off of it. this one really messed me Up.i Hope this helps someone Out There, I Just Spen Trying to Figure THIS OUT. Good Luck! Posted by Sean Countryman ON May 7 2004 8:50 PM [delete] [edit]
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 That I Had Coded Costain Data Transactions In Different Manners in Different Areas of The Application Even Though The Yy Should Have Been The Same. SP's A llowed 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 his name "Cursors are Evil". The fact is that you can probably rewrite nearly foget 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.Posted by dave mausner on June 29 2004 11..: 58PM [delete] [edit]
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 do not yet know how MySQL compiles cursors, but Oracle cursors can be many times FASTER in the example i just gave.Posted by Tsoi Pui Hang on August 12 2004 9:17 am [Delete ] [Edit]
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)) begindeclare 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_idfrom 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_setwhere catagory.name = param2 and catagory.catagory_id = catagory_set. slave_idand catagory_set.master_id = master_id; if master_exist> 0 and name_exist = 0 thenlock 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 TENSET ERROR_MSG = '
The master catagory ID provided does not exist '; elseif name_exist> 0 thenset 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 catagorywhere name = param2 and not exists (select * from catagory_setwhere catagory_set.slave_id = catagory.catagory_id); if name_exist> 0 thenset error_msg = 'The catagory name already exist, please choose another name'; ElseInsert 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.
TOP
/
Previous
/
NEXT
/
UP
/
Table of contents
© 1995-2004 MySQL AB. All Rights Reserved.
MySQL.com Home Site Map Contact US Privacy Policy Trademark Info
