MySQL Stored Procedure is a new
feature release in version 5.0, positioning the database a
step closer in features to the leading database products. In
this article, you’ll be guided on how stored
procedures are created using several tools described below.
Before we start the tutorial, let’s have a quick
check on the tools you will need. Listed below are the
following you can download from the Web. Also, the code used
in this tutorial were tested by these tools, and your
results may vary depending on how close or far you are from
the tools mentioned below.
The article assume you already have the tools installed
and you are now ready to follow the steps. However, you can
still follow and learn what makes sense if you are running a
different copy of Linux or a different MySQL Administrator
version.
What this article will show you are the following steps:
- Create a new schema.
- Create a new table.
- Create a new stored procedure.
- Testing the stored procedure.
Create A New Schema
Launch MySQL Administrator from Applications->
Programming-> MySQL Administrator, then select View from
the menu bar. The View menu appears, now select Catalogs
from the list. You’ll see a list show at the bottom
left of the panel. Right click on that list and select
Create Schema. A dialog box appears containing a textbox for
entering the schema name. Enter a name in the textbox and
click the OK button.
Create A New Table
This step will create a new table. Select the newly
created schema from the schema list located at the lower
left part of the panel. You’ll see five buttons at the
lower part, select Create Table. The table editor dialog box
shows up, from there you can start naming the table and also
add the columns as well. Use the table definition below to
populate the dialog box. Click Apply Changes to save and
create the table.
CREATE TABLE `mmorpg_users` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`user_login` varchar(60) NOT NULL default '',
`user_pass` varchar(64) NOT NULL default '',
`user_nicename` varchar(50) NOT NULL default '',
`user_email` varchar(100) default NULL,
`user_url` varchar(100) default NULL,
`user_registered` datetime NOT NULL default '0000-00-00
00:00:00',
`user_activation_key` varchar(60) default NULL,
`user_status` int(11) NOT NULL default '0',
`display_name` varchar(250) default NULL,
`latest_ping` datetime default NULL,
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
Create A New Stored Procedure
There are four tabs at the top part of the form. Select
the Stored Procedures tab to open the list. Click the Add
Procedure button and a routine name dialog box will show up,
enter the name of the procedure and select OK. At this
point, you can simply copy-and-paste the stored procedure
code shown below. Click Execute button to commit the code.
CREATE DEFINER=`root`@`localhost`
PROCEDURE `setUser`(OUT _id int, _uid varchar(60),
_pwd varchar(64),
_nm varchar(50),
OUT _retcode int)
BEGIN
declare _cnt int;
declare _errno int;
set _retcode = 0;
case _id
when 0 then
select count(*) into _cnt from mmorpg_users
where user_login = _uid;
if _cnt = 0 then
insert into mmorpg_users (user_login, user_pass,
user_nicename)
values (_uid, _pwd, _nm);
select last_insert_id() into _id;
else
set _retcode = 1;
select ID into _id from mmorpg_users
where user_login = _uid;
end if;
else
update mmorpg_users
set user_login = _uid,
user_pass = _pwd ,
user_nicename = _nm
where ID = _id;
end case;
select * from mmorpg_users where ID = _id;
END
Testing The Stored Procedure
At this point, you are now going to test the stored
procedure. Open the Terminal program from
Applications->Accessories->Terminal. Run mysql with
the following:
desktop:~$ mysql -u root
Then enter the following text inside mysql.
mysql> call setUser(@id, 'John', 'btd19x', 'redball',
@retcode);
Conclusion
This is just one sample implementation of a MySQL stored
procedure, walking you through the steps. With these tools,
you can now expand to even create more procedures for your
own applications. Perhaps even write sophisticated ones as
well.
