StoredProcedures
Contents |
[edit] Introduction
MySQL was first released back in November of 1996 and has grown ever since. It has become the database of choice for web development due to ease of use and support by many ISPs and web hosting companies. AB the company who control and develop MySQL claim it is the worlds most popular open source database.
However one thing it has had difficulty in the past is convincing ´serious´ database developers that it has the capacity to challenge the database market leaders, Oracle, Microsoft´s SQL Server and IBM´s DB2. There are a number of reasons for this reputation, some justified but others not so.
One area of functionality that the big players all support is stored procedures within the database, this is something MySQL has lacked. Until now, with the release of MySQL 5.0.1 support has been added for stored procedures within the database. At this early stage it´s still limited in capability and has some important parts missing but AB seem to be approaching the subject by getting the basics right first before increasing the functionality.
For the average web developer the introduction of stored procedures may seem a little bit of an anti climax, they may on the surface seem surplus to requirements with most of the solutions they provide being relatively easy to replicate in PHP or some other web language. But support for stored procedures will be seen as a big step forward in the database community where data is king, rather than a means to maintain data within a web application.
[edit] So What Are They?
Stored procedures are sections of code which are stored in the database and executable within the database. They can perform simple changes to single values or undertake large processing tasks. They fall in to two categories, functions and procedures. We will look at the difference between the two later in the tutorials.
It is in fact possible to use and create functions within MySQL using C but that can be a little complicated and has limited functionality. There are also a number of functions available within MySQL that you may have used before, such as GREATEST() or CONCAT().
[edit] Getting Going
Unfortunately the setup and installation of MySQL is a big subject and something we don´t have room to go into detail about here, but there are plenty of web sites which can give you all the information you need.
To program with stored procedures in MySQL we need to use version 5.0.1 or above, we recommend you use as high a release as possible, this can be downloaded from the MySQL web site for free. In addition to MySQL we will be using a basic text editor, this is not necessary but will make our life a little easier.
One of the advantages of using stored procedures is that they are run from within the database so any commands we use on one operating system will be available on another. The tutorials have been run under Windows XP but they should run exactly the same on any other operating system you may be using. One final word, if the only copy of MySQL you have is a lower version than 5.0.1 then unfortunately you won´t be able to try the examples yourself, but you can still read and learn all about stored procedures from the tutorials.
[edit] Conventions
Finally a word on the conventions we will be using in the tutorials. Any code that you can type in will be displayed in one of two ways
//This is code seen for the first time. //Or code you have already seen but is being reference later
Changes to programs previously created during the tutorial will be shown in bold
//This is old code here //This is the line we want you to add
You can download a zipped copy of all the sources used in each chapter using the menu. But after each section of code will be a link to the actual source code file that we used. We suggest you try typing in each section of code yourself as this can often help you understand what is actually going on, but if you can´t get a particular program to work feel free to use our code. The source will appear as follows.
source.msp
So lets get straight in by having a go at creating a simple procedure, the venerable HelloWorld application!