MySQL 5.0 Stored Procedure Programming Tutorial

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.

• Ubuntu 7 ( Operating System )
• MySQL version 5 ( RDBMS )
• MySQL Administrator ( GUI )

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,
  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.

Futurescape

Every morning, Sam takes a walk down to the park before turning left at the corner. Sam takes the morning newspaper, clipped under while the left hand holds the coffee. The elevator door opens leading into the company lobby at the 48th floor of the tallest building in town. Carrying a shoulder strap leather bag, he places it on top of a wooden table, plugs the cable to the ethernet, flips the cover before touching the On button. The screen opens up with a splash screen showing an image of a penguin with a logo at the bottom–Linux.

Sam is not alone, he is only just one of the many who use Linux as their primary environment. He is joined by his officemates who run standard OEM machines provided by several companies. The Chief Technology Officer also carry a notebook loaded with Linux.

“All of our notebooks here run Linux,” Sam said. “A lot of our applications run natively on several hardware configurations, although we still use other OS for legacy applications such as Windows and MacOS,” added Sam.

It wasn’t long ago, thirty years to be exact when computing was done on a different level. The economics of software development were different, a product of a system that has long been replaced by a new paradigm. “My parents told me they used to pay a lot and they bought in pieces,” Sam said. “At that time, before the great correction, my parents had to endure a lot from creating programs that may infringe. They fought hard against those who seek to subvert the true essence of the law and they won,” added Sam.

Sam and his company are now enjoying the fruits of those who fought for freedom. It wasn’t because they believe the old regime will stay the same, they believed on the idea that they could be free to use and share the works they produced. A new culture emerged from it and is now considered the majority culture.