MySQL 6.0 Reference Manual :: 22 Stored Procedures and Functions

Previous / Next / Up / Table of Contents

User Comments

Posted by Tsoi Pui Hang on August 12 2004 5:17am

[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))
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;

Posted by Nathan Wallbridge on September 17 2004 6:59am

[Delete] [Edit]

Here is some code I used to geocode a dataset based on the NG Field and parcel number (UK Ordinance survey) Useful if you are working with GIS data.

I have cut out some of the case statement as it is very repetitive and you can easily look it up, mainly this example show how you can manipulate data then use your result to update existing tables.

delimiter //

CREATE PROCEDURE GEOCODE03()
BEGIN
DECLARE X_digit_one CHAR(1);
DECLARE Y_digit_one CHAR(1);
DECLARE INTOSSHEET INT;
DECLARE INTNGFIELD INT;
DECLARE strOSSHEET CHAR(50);
DECLARE strNGFIELD CHAR(50);
DECLARE lngGeoLoop INT;
DECLARE lngXINT INT;
DECLARE lngYINT INT;
DECLARE INTCOUNT INT;
DECLARE INTNUMROWS INT;
DECLARE intMIKEY INT;
DECLARE STRXINT CHAR(50);
DECLARE STRYINT CHAR(50);
DECLARE FIRSTDIG CHAR(2);
DECLARE GeoCodeCUR CURSOR FOR SELECT lpaSheetReference,lpaFieldNumber,UID FROM MIXCheckDB.IACS2003GIS;
OPEN GeoCodeCUR;
SELECT COUNT(*) INTO INTNUMROWS FROM MIXCheckDB.IACS2003GIS;
SET INTCOUNT = INTNUMROWS;
WHILE INTCOUNT > 0 DO
FETCH GeoCodeCUR INTO strOSSHEET,strNGFIELD,intMIKEY;
SET FIRSTDIG = LEFT(strOSSHEET,2);
CASE FIRSTDIG
WHEN "SR" THEN
SET X_digit_one = "1";
SET Y_digit_one = "1";
WHEN "SM" THEN
SET X_digit_one = "1";
SET Y_digit_one = "2";
WHEN "SS" THEN
SET X_digit_one = "2";
SET Y_digit_one = "1";
WHEN "SN" THEN
SET X_digit_one = "2";
SET Y_digit_one = "2";
....(Lots more of the case statement here)...
ELSE
SET X_digit_one = "0";
SET Y_digit_one = "0";
END CASE;
SET STRXINT = CONCAT(X_digit_one, MID(strOSSHEET,3,2), LEFT(strNGFIELD,2), '0');
SET STRYINT = CONCAT(Y_digit_one, RIGHT(strOSSHEET,2), RIGHT(strNGFIELD,2),'0');
SET lngXINT = STRXINT;
SET lngYINT = STRYINT;
UPDATE MIXCheckDB.IACS2003GIS SET xcoord = lngXINT, ycoord = lngYINT WHERE MIXCheckDB.IACS2003GIS.UID = intMIKEY;
SET INTCOUNT = INTCOUNT - 1;
END WHILE;
CLOSE GeoCodeCUR;
END

Posted by Mike Heath on October 26 2005 10:31pm

[Delete] [Edit]

MySQL as of version 5.0.15 does not support recursive stored procedures. This is contrary to the 'hierarchy2' example found here http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf

Posted by Graham Jordan on November 16 2006 1:17pm

[Delete] [Edit]

HERE IT IS:
For all of you getting "can't return a result set in the given context" errors when using PHP to execute stored procedures,
the mysql_connect flag is:

mysql_connect( host, databaseuser,password,TRUE, 131074)

Worked with mysql 5.0.20 and PHP 5.1.4
Also, stored procedures seem to close the connection when they've finished running in PHP.
Can be fixed using mysql_ping( db_resource_id ) to reinstate lost connections

Posted by Christian Kurmann on August 14 2007 2:54pm

[Delete] [Edit]

Thanks for the great comments and example of the people posting here. As my predecessor has already noted, this seems to be the best place for information about MySQL SPs by far.

I would also like to join the spirit and add an example of how to create an MySQL Stored Procedure Insert command for use by a vb.net dataadaptor which can use the parameter output value of the auto incremented Primary key to update the underlying dataset. (This is a Standard procedure for using datasets and dataadapters with cached Data inserts.)

First I would like to point out a few tripwires along the way.
The MS-SQL SCOPE_IDENTITY() and the @@IDENTITY do not work in MySQL. There is also a MySQL_Insert_ID command which also can not be used in Stored procedures
You have to use LAST_INSERT_ID();

The usual @Parametername denomination using a @ to define a paramter which is standard in .net, can not be used as the @ is not recognised by MySQL as procedure parameter. Use ? instead! However use the ? only in the .net parameter declaration and leave it away when defining the stored procedure in MySQL. Also beware not to use a valid field name as parameter name as this will result in false values.

Now to get on to the example:
I want to create a vb.net dataadapter (following an example from the excellent ADO book PRO ADO.NET 2.0 by Sahil Malik for APRESS) to insert new values in a dataset table into a MySQL table and at the same time retrieve the auto created Primary keys and update the dataset table with these accordingly.

The Following table was used:
--------------------------------------------
DROP TABLE IF EXISTS `test`.`animals`;
CREATE TABLE `test`.`animals` (
`AnimalID` int(10) unsigned NOT NULL auto_increment,
`AnimalType` varchar(45) NOT NULL,
`TimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`AnimalID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
--------------------------------------------

Create the Following Procedure:
--------------------------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`UP_ANIMALSINSERT`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`UP_ANIMALSINSERT`(OUT _AnimalID INT,_AnimalType VARCHAR(45))
BEGIN
INSERT INTO animals
(AnimalType)
VALUES
(_AnimalType);
SET _AnimalID=LAST_INSERT_ID();
END $$

DELIMITER ;
------------------------------------------
Please note that some examples create stored procedures with SELECT LAST_INSERT_ID() INTO _NewID;
While this should also return the correct new value, this can not be used for the dataadapter example as this expects the value as a parameter.

Here the VB.Net Code to use the Stored Procedure:
------------------------------------------
Public Shared Sub SaveData()
'Create new MySQL Connection with connectionstring from Application Settings
Using con As New MySqlConnection(My.Settings.testConnectionString)
Dim myDA As MySqlDataAdapter
con.Open()
'Begin Transaction
Dim myTrans As MySqlTransaction = con.BeginTransaction
Dim myparam As MySqlParameter

'Create New Insertcommand with Paramters to this table. Please note the Paramterdirection and the "?_" used to name the paramters
'Animals
Dim myInsertAnimals As MySqlCommand = New MySqlCommand
myInsertAnimals.Connection = con
myInsertAnimals = New MySqlCommand("UP_ANIMALSINSERT")
myInsertAnimals.CommandType = CommandType.StoredProcedure

myparam = New MySqlParameter("?_AnimalID", MySqlDbType.Int32, 5, "AnimalID")
myparam.Direction = ParameterDirection.Output
myInsertAnimals.Parameters.Add(myparam)

myparam = New MySqlParameter("?_AnimalType", MySqlDbType.VarChar, 45, "AnimalType")
myparam.Direction = ParameterDirection.Input
myInsertAnimals.Parameters.Add(myparam)

myInsertAnimals.Transaction = myTrans

Try
'Create new Dataadapter with select do define correct table.
myDA = New MySqlDataAdapter("Select * from Animals", con)
myDA.InsertCommand = myInsertAnimals
myDA.InsertCommand.Connection = con
'Insert the Data by calling Update and supplying only the new (Added) records in the datatable
myDA.Update(ds.animals.Select("", "", DataViewRowState.Added))

myTrans.Commit()
Catch ex As Exception
myTrans.Rollback()
Finally
con.Close()
End Try
End Using
End Sub
------------------------------------------
Please note that using the transaction is not required in this example but when using this sort of update for hierarchical data where the ident field of added records in the cached dataset are used as foreign key in a second table.

Hope this helps someone, as I have just spent a day trying this stuff out.

Posted by Geoffrey Hoffman on August 21 2007 4:39am

[Delete] [Edit]

In trying to learn Stored Procedures, I discovered that you can use variables easily in the WHERE clause but it's more tricky to use them in LIMIT clause. If you want to use variables in the LIMIT clause (for dynamic pagination, for example) you have to use PREPARE statement, EXECUTE statement, and DEALLOCATE PREPARE statement within the SP.

For example, this works fine:

DELIMITER //
DROP PROCEDURE IF EXISTS DATA.GETCATEGORYFORPARENTID //
CREATE PROCEDURE DATA.GETCATEGORYFORPARENTID ( IN _parentId INT(3) UNSIGNED )
BEGIN
SELECT * FROM category WHERE parent_id = _parentId ;
END;
//



But, if you want to pass in the LIMIT values _start and _limit you have to do:



DROP PROCEDURE IF EXISTS DATA.GETCATEGORY//
CREATE PROCEDURE DATA.GETCATEGORY(_start INT(2), _limit INT(2))
BEGIN
SET @_start = _start, @_limit = _limit;
PREPARE stmt FROM 'SELECT * FROM category WHERE parent_id = 0 ORDER BY ord ASC LIMIT ?, ?';
EXECUTE stmt USING @_start, @_limit;
DEALLOCATE PREPARE stmt;
END;
//

(Thanks to William C on the SP forum for the tip.)

If you try to do...

SELECT * FROM category WHERE parent_id = 0 LIMIT _start, _limit

...mysql5 generates an error.

The documentation for SELECT Syntax states "LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements)" but it's not obvious when using variables inside a SP.

Posted by Chris Shattock on October 6 2007 6:46pm

[Delete] [Edit]

Coming from an MSSQL background from 1995 and prior to that DB2 and SQL/DS I only use MySQL now becuase of SP's and Functions in >= 5. However, the "can't return a result set..." issue really "bugs" me - it is almost as if the stored, optimised procedure cannot tell the parser what the nature of the expected output is! I find that for scalar procedures and singletons this makes their use tiresome - and dynamic SQL within a "high-performance" procedure is unacceptable.

Notwithstanding - I find functions in MySQL useful and better than MSSQL. For example, having given up on returning a boolean after checking the existence of an email address in a table column via a "scalar" SP I tried a function and was pleasantly surprised that the following worked (which in MSSQL it won't):

Create Function fn_UserEmailExists (
_Email VarChar(255)
)
Returns Boolean
Begin
Declare _Exists Boolean Default If(Exists(Select `id` From `table` Where `column` = _Email), true, false);
Return _Exists;
End;

Invocation is then simply via;

Select fn_UserEmailExists('user@domain.com');

I like this in MySQL and will have to revise my (historical) prediliction for scalar stored procedures.

Posted by Steve Morgan on October 22 2007 9:10pm

[Delete] [Edit]

After an hour of pondering, I have finally clocked why my php code was failing to return records via my newly created stored procedure - user did not have access to execute stored procs.

Look up the syntax to add privileges.

Posted by Ivan Grinkin on November 29 2007 10:56am

[Delete] [Edit]

HELLO...

I have some comments to split_string array-to-SP hack.

I'm use 5.0.44-log mysql server on gentoo linux.

When I'm trying to use split_string, I obtain this bug:

mysql> call split_string('a,b,c,d,e,f',',');
Query OK, 1 row affected (0.00 sec)

mysql> select * from SplitValues limit 50;

+-------+
| value |
+-------+
| a     |
|   c   |
| d     |
| e     |
| f     |
+-------+

5 rows in set (0.00 sec)

So, 'b' character can't splitted, and c character was insert to table with spaces.

When I was test with many characters words - SP works properly.

For fix this bug I do this:

SET remainder = SUBSTRING(input,1,CHAR_LENGTH(input));

instead

SET remainder = input;

Any comments?

wbr, Ivan

Posted by Christopher Hallam on January 18 2008 2:55am

[Delete] [Edit]

I am rather new to development and databases.
My only comment is that it seems quite amazing that MySQL has only recently implemented SPROCS, Functions, etc into their databases. But one thing I am learning very rapidly is that if you can master how to effectively create and use Sprocs and Functions, you can eliminate a lot of painful front-end application code. This, of course, is relative to the workflow you expect the server to receive and be able to handle. In large user environments (the web for example) it would potentially detract from performance to rely too heavily on server side routines in this manner. But for personal, or small scale intranet environments, this seems ideal.

Posted by Matus Ivanecky on April 6 2008 8:57am

[Delete] [Edit]

Here is example of STORED procedure for pagination traversal of recordset for specified SQL Query:

[* Please post optimisation hints if you can find some]

CREATE DEFINER = CURRENT_USER
PROCEDURE example.executeSQLwithScrolling(
IN sqlQuery TEXT, -- sql query for execution
INOUT pageSize INT, -- from: 0 - m
INOUT pagePosition INT, -- from: 1 - n
OUT recordsCount INT, -- total records count for specified query
OUT pagesCount INT -- total pages count for specified query
)
BEGIN

set @recCount = 0;
set @pageSizeDef = 25;

set pageSize = ifnull(pageSize,@pageSizeDef);
set pagePosition = ifnull(pagePosition,0);


if( pageSize <= 0 ) then
set pageSize = @pageSizeDef;
end if;

if( pagePosition <= 0 ) then
set pagePosition = 0;
else
set pagePosition = pagePosition - 1;
end if;


set @sqlFromPart = substring( sqlQuery , locate('from',sqlQuery));
set @sqlTemp = concat('select count(*) into @recCount ', @sqlFromPart);

prepare stfm1 from @sqlTemp;
execute stfm1;
deallocate PREPARE stfm1;

set recordsCount = @recCount;
set pagesCount = @recCount DIV pageSize;
if (@recCount%pageSize > 0) then
set pagesCount = pagesCount + 1;
end if;

set @startPos = pageSize * (pageposition -1);
set @pageSize = pageSize;

set @sqlTemp = concat(sqlQuery, ' LIMIT ?,?' );
prepare stfm1 from @sqlTemp;
execute stfm1 using @startPos, @pageSize ;

deallocate PREPARE stfm1;

END

Posted by Nigel Croft on April 28 2008 3:44pm

[Delete] [Edit]

This takes a standard URL and splits it down into it's component parts (scheme, host, path (including filename), and query string). Any port number present is discarded.

mysql> delimiter ;;
mysql> create procedure uri(in url VARCHAR(512))
-> BEGIN
-> SELECT substring(url,1,locate('://',url)-1) AS scheme,
-> substring_index(substring_index(substring_index(url,'://',-1),'/', 1),':',1) AS host,
-> substring_index(substring(substring_index(url,'://',-1),locate('/',substring_index(url,'://',-1))),'?',1) AS path,
-> substring(substring(url,locate('?',url)),2) AS query;
-> END;;
Query OK, 0 rows affected (1.09 sec)

mysql> delimiter ;
mysql> CALL uri('http://www.chocolate-now.co.uk ');

+--------+-------------------------+------+-------+
| scheme | host                    | path | query |
+--------+-------------------------+------+-------+
| http   | www.chocolate-now.co.uk |      |       |
+--------+-------------------------+------+-------+

1 row in set (0.56 sec)

Query OK, 0 rows affected (0.56 sec)

mysql> CALL uri('http://www.chocolate-now.co.uk/cgi-bin/shop-cart/ShopCart.pl?mainframe=view_main ');

+--------+-------------------------+--------------------------------+---------------------+
| scheme | host                    | path                           | query               |
+--------+-------------------------+--------------------------------+---------------------+
| http   | www.chocolate-now.co.uk | /cgi-bin/shop-cart/ShopCart.pl | mainframe=view_main |
+--------+-------------------------+--------------------------------+---------------------+

1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql>

Add your own comment.

Top / Previous / Next / Up / Table of Contents