Monday 29th of May 2017 10:44:12 PM MySQL Stored Procedures: Part 1
August 9, 2005

What are Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS's raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here - if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.

A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn't been an option. That has been limiting. Some have claimed that there are two schools of thought - one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?

Why use stored procedures?

A simple example

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let's set up a basic stored procedure first. This one will simply say 'Hello' in the Xhosa language - Molo.

mysql> CREATE PROCEDURE molo() SELECT 'Molo';
Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:

mysql> CALL molo()\G
*************************** 1. row ***************************
Molo: Molo
1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.

Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.

There are three types of parameter:

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.

mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molweni
1 row in set (0.00 sec)

An IN example

Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.

mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_in('Molo');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molo
1 row in set (0.00 sec)

The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.

An OUT example

mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P='molo';
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_out(@x);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: molo
1 row in set (0.00 sec)

We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.

An INOUT example

mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_inout(2);
are limited in the range of named colors available. For example,setting a color to "mother-of-pearl" isn't going towork, because it isn't a defined color. (Well, not yet, at anyrate.) Technically speaking, there are nodefined colors, but there are 16 colors that are suggested by thespecification and that all major browsers recognize:

If these seem like odd color names, it's because -- well,they are. In my opinion, anyway. So where do they come from? These Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x\G *************************** 1. row *************************** @x: 4 1 row in set (0.00 sec)

Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.

XML supports shareable structure (using DTDs)

Since the structure of the XML document can be specified in DTDs they provide a simple way to make it easier to exchange XML documents that conform to a DTD. For example, if two software systems need to exchange information, then if both of the systems conform to one DTD, the two systems can process information from each other. DTDs are not as powerful as some kind of schema architecture for XML, they don't support typing, subclassing, or instantiation mechanisms that a schema architecture must have.

DTDs are a simple way to make sure that 2 or more XML documents are of the same "type". Its a very limited approach to making "typed" XML documents shareable across systems. In the future some kind of schema system will be proposed by the W3C that should allow typing, instantiation and inheritance of information (in XML).

XML enables interoperability

dashed line has been added to illustrate the edges of the clippingregion. This line would not actually appear in a user agentattempting to render the document.

Figure 9-11

Figure 9-11. Contracting the clipping region

The syntax of rect is an interesting case.Technically, it can berect(top,right,bottom, if it's a replaced element) is lowered with respect to its parent's baseline. However, the distance it is lowered is not defined in the specification, so it may vary from one user agent to another. Note that sub does not imply a change in the element's font size, so it should not cause subscripted text to become smaller (or larger). Instead, any text in the subscripted element should be, by default, the same size as text in the parent element, as shown in Figure 4-32:

to see what happens with padding set on an inline element displayed across multiple lines:

B {padding: 10px; background: silver;}
Figure 7-61

Figure 7-61. Padding on an inline element that spans multiple lines

As with margins, the left padding is applied to the beginning of the element, and the right padding to the end of it; padding is not applied to the right and left side of each line.