MySQL 5.0 Stored Procedure Programming Tutorial

May 23, 2007

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:

  1. Create a new schema.
  2. Create a new table.
  3. Create a new stored procedure.
  4. 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,
  KEY `user_login_key` (`user_login`)

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)
	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;
					set _retcode = 1;
					select ID into _id from mmorpg_users
						where user_login = _uid;
			end if;
			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;

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


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.