Friday 22nd of September 2017 11:55:55 AM 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;

This isn't unusual, but the situation gets interesting when youuse the last two values we have to discuss: bolderand lighter. In general terms, these keywords havethe effect you'd anticipate: they make text more or less boldwith comparison to its parent's font weight. Let'sconsider bolder first.

Query OK, 0 rows affected (0.00 sec) mysql> CALL sp_inout(2); 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.


 First, note that HTML ignores 
carriage returns           and 
extra spaces.<BR>You have to specify line breaks. First, note that HTML ignores carriage returns and extra spaces. 
You have to specify line breaks.  layout.

A perfect example is an unordered list, in which the list items follow one another. Assume that the following is declared for a list that contains five list items:

LI {margin-top: 10px; margin-bottom: 15px;}

Thus, each list item has a 10-pixel top margin and a 15-pixel bottom margin. However, when the list is rendered, the distance between adjacent list items is 15 pixels, not 25. This is because along the

WARNING

Something else to watch out for is Navigator 4's handling ofvalues for color that it doesn't recognize.If Navigator 4 encounters an unknown word (such asinvalidValue) somehow, through mechanisms knownonly to itself, it actually arrives at and uses a color. Itdoesn't do so randomly, exactly, but the effect is practicallythe same. For example, invalidValue comes out as adark blue, and inherit, which is a valid value CSSalso allows for comments, but it uses a completely different syntaxto accomplish this. CSS comments are very similar to C/C++ comments,in that they are surrounded by /* and*/:

/* This is a CSS1 comment */

Comments can span multiple lines, just as in C++:

/* This is a CSS1 comment, and itcan be several lines long without
Use DOM to directly manipulate the information stored in the document (which DOM turns into a tree of nodes). This document object is created by the DOM XML parser after it reads in the XML document. This option leads to messy and hard-to-understand code. Also, this works better for document-type data rather than just computer generated data (like data structures and objects used in your code).
  • Create your own Java object model that imports information from the XML document by using either SAX or DOM. This kind of object model only uses SAX or DOM to initialize itself with the information contained in the XML document(s). Once the parsing and initialization of your object model is completed, DOM or SAX isn't used anymore. You can use your own object model to accessed or modify your information without using SAX or DOM anymore. So you manipulate your information using your own objects, and rely on the SAX or DOM APIs to import the information from your ApplicationML file into memory (as a bunch of Java objects). You can think of this object model as an in-memory instance of the information that came was "serialized" in your XML document(s). Changes made to this object model are made persistent automatically, you have to deal with persistence issues (ie, write code to save your object model to a persistence layer as XML).
  • Create your own Java object model (adapter) that uses DOM to manipulate the information in your document object tree (that is created by the parser). This is slightly different from the 2nd option, because you are still using the DOM API to manipulate the document information as a tree of nodes, but you are just wrapping an application specific API around the DOM objects, so its easier for you to write the code. So your object model is an adapter on top of DOM (ie, it uses the adapter pattern). This application specific API uses DOM and actually accesses or modifies information by going to the tree of nodes. Changes made to the object model still have to be made persistence (if you want to save any changes). You are in essence creating a thin layer on top of the tree of nodes that the parser creates, where the tree of nodes is accessed or modified eventually depending on what methods you invoke on your object model.
  • Depending on which of the three options you use to access information using your Java classes, this information must at some point be saved back to a file (probably to the one from which it was read). When the user of your application invokes a File->Save action, the information in the application must be written out to an ApplicationML file. Now this information is stored in memory, either as a (DOM) tree of nodes, or in your own proprietary object model. Also note that most DOM XML parsers can generate XML code from DOM document objects (but its quite trivial to turn a tree of nodes into XML by writing the code to do it yourself). There are 2 basic ways to get this information back into an ApplicationML file: