A DIY Approach to Stored Procedures in MySQL

If you have avoided using MySQL in the past due to its lack of support for stored procedures, here’s good news. The latest developer release of MySQL (MySQL 5.0) supports stored procedures. Drum roll, please! If you want to jog your memory before you begin to create those cretins or want to know more about them, read on. In this article, we’ll learn what stored procedures are and create our first stored procedure in MySQL and learn some useful commands while we’re at it.

What is a Stored Procedure?

A stored procedure is a set of SQL statements stored on the server that takes in certain arguments and processes that code with those arguments at execution time. It can be invoked simply by making a call to the procedure with the correct arguments. The difference between stored procedures and other sets of SQL statements is that stored procedures reside on the server and are pre-compiled.

Stored procedures are an integral part of the Oracle and MS SQL databases. MySQL has joined the bandwagon with its latest developer release. MySQL currently supports the SQL: 2003 syntax but will support T-SQL and Oracle PL/SQL syntaxes in the future.  However, I should warn you that this release is still in the alpha stage and is not ready for prime time just yet. Even then, it helps to understand a little more about stored procedures since a mature release may be available in the not–so-distant future.

Why should I care?

You have been getting by just fine without any knowledge of stored procedures, thank you very much. But take a minute to understand how stored procedures in general could benefit you:

  1. Better performance  Stored procedures are faster because they are pre-compiled SQL code. This reduces the “Compile and Execute” step to just “Execute’ in most cases.  Also, only the call to the stored procedure needs to be sent to the server instead of chunks of information – this reduces the information that needs to be sent to the server and acts like a call to a remote procedure. This is an advantage when it comes to code that is called repeatedly. However, the load on the server is another point to consider since most of the processing will now be done on the server.

  2. Easier to maintain  Since all the SQL can now be stored on the server, it is easier to make changes to the stored procedure than to a bunch of SQL statements distributed all over the application. 

  3. Security  Although the use of stored procedures is not by itself a guarantee of security, it can be used to create an environment where applications and users can only access database tables through the stored procedures, instead of giving them direct access to the tables. The benefit is a layer of abstraction.

  4. Optimization  When a SQL statement is parsed by the server, it is optimized internally by the server. If a bunch of SQL statements are sent to the server, repeatedly, they have to be optimized each time. The SQL statements in the stored procedure that is in memory have to only be optimized once and an execution plan is created for the SQL statements in the stored procedure.

But??

Frans Bouma has done a great job of listing the cons associated with the usage of stored procedures, with great passion – I might add – and so instead of repeating it, I will let you read it at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx.

Using stored procedures is just one way to manipulate data. Good or bad – I leave it for you to decide. The scope of this tutorial is to have you creating stored procedures in MySQL in no time, should you want to. However, it is my opinion that there are times when the use of stored procedures is warranted for the reasons outlined above.

{mospagebreak title=Create a Stored Procedure}

Creating your first stored procedure

To learn about stored procedures in MySQL, let’s start at the beginning. The first order of business is to create a stored procedure in MySQL. The parser checks for syntax errors when the procedure is being created and checks for existence of variables, parameter count etc. when it is executed for the first time.

Bring up the command window for MySQL. Change to the database that you want to use. Create a widget table (or a table named widget).

mysql> CREATE TABLE WIDGET (
    ->  WIDGET_ID int(11),
    ->  WIDGET_PRICE decimal(6,2));
Query OK, 0 rows affected (0.45 sec)

Populate it with the following values using the INSERT statement.

mysql> INSERT INTO WIDGET VALUES (1,253.00);
Query OK, 1 row affected (0.00 sec)

mysql> select * from widget;
+———–+————–+
| WIDGET_ID | WIDGET_PRICE |
+———–+————–+
|         1 |       253.00 |
|         2 |       202.00 |
|         3 |       734.40 |
+———–+————–+
3 rows in set (0.00 sec)

mysql> INSERT INTO WIDGET VALUES (4,234.00);
Query OK, 1 row affected (0.00 sec)

Create the stored procedure by using the syntax below.
mysql> DELIMITER |
mysql> CREATE PROCEDURE PARTPRICE
    -> (partid INT ,
    -> Quantity INT,
    -> price DECIMAL(6,2)
    -> )
    -> BEGIN
    -> DECLARE discount_percent DECIMAL(6,2);
    -> DECLARE discounted_price DECIMAL(6,2);
    -> SET discount_percent  =  15;
    -> SET discounted_price = price – discount_percent /100*price;
    -> IF quantity > 2 THEN
    -> SET discounted_price = discounted_price – 2.00;
    -> END  IF;
    -> UPDATE WIDGET
-> SET widget_price = discounted_price WHERE widget_id = partid;
-> Select * from widget;
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

The following stored procedure called PARTPRICE, accepts a part id, the number of parts purchased and the price of the part and calculates the discounted price for that part and updates the Widget table. It also displays the table with the updated values, which is possible since MySQL allows the use of simple SELECT statements without cursors or local variables inside a stored procedure.

The DELIMITER statement serves to change the delimiter from the regular “;” to the character that you specify after the DELIMITER keyword. The purpose of this is to allow MySQL to use a different delimiter to indicate the end of the procedure, since the semi-colon is being used within the stored procedure to indicate the end of a statement to the parser. This allows us to use a group of statements inside the BEGIN … END block. You can choose any delimiter you like. When you call a procedure, it can only pass back values using output variables. Stored procedures can also call other stored procedures, thus extending the power of stored procedures many-fold.

The scope of the declared variables is within the BEGIN .. END block. Only one BEGIN .. END block may exist within a stored procedure. However, you may add a label to the BEGIN .. END statements. The labels should match if both are specified. Tables are defaulted to the database used by the caller. A different database can be specified using the Use database directive or the database_name.table_name syntax.

Label: BEGIN

Statement(s)

End Label

Passing parameters

The parameter list should always be present for a procedure even if there are no parameters to be specified. A parameter is an IN parameter by default and is passed by reference; that is, a pointer to the IN parameter is passed to the corresponding formal parameter. However, the OUT and IN OUT parameters are passed by value.

{mospagebreak title=Calling and Dropping a Procedure}

Calling a procedure

The benefit of a stored procedure truly hits you when you call your first stored procedure.

mysql> CALL PARTPRICE( 3, 1, 864);
+———–+————–+
| WIDGET_ID | WIDGET_PRICE |
+———–+————–+
|         1 |       253.00 |
|         2 |       536.90 |
|         3 |       734.40 |
+———–+————–+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

See how simple it is? This is a great way to group together code that is common to many parts of your application so that you can call it from different places in your application with different parameters with just one statement. This also makes it easier to change the code in just one place instead of in several places.

Dropping a procedure

If you made a non-syntactical mistake while creating your first procedure don’t lose heart.

You can just drop that procedure and create a new procedure with the same name.

IF you are not sure if a procedure exists, it is probably a good idea to insert the “IF EXISTS” clause in your DROP PROCEDURE statement. This tells MySQL to only drop a procedure if it existed in the first place. If the procedure does not exist, a warning is generated instead of an error, which can be viewed by typing the “SHOW WARNINGS” command.

mysql> DROP PROCEDURE IF EXISTS PARTPRICE;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE PARTPRICE
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW WARNINGS;
+———+——+——————————-+
| Level   | Code | Message                       |
+———+——+——————————-+
| Warning | 1289 | PROCEDURE part does not exist |
+———+——+——————————-+
1 row in set (0.00 sec)

Some useful statements

mysql> SHOW CREATE PROCEDURE PARTPRICE;

It shows you the statement used to create the procedure. This is useful to have if you didn’t create the procedure in the first place but want to know how.

| Procedure | Create Procedure
| PARTPRICE | CREATE PROCEDURE `PARTPRICE`(partid INT ,
     Quantity INT,
     price DECIMAL(6,2)
     )
BEGIN
     DECLARE discount_percent DECIMAL(6,2);
     DECLARE discounted_price DECIMAL(6,2);
     SET discount_percent  =  15;
     SET discounted_price = price – discount_percent /100*price;
     IF quantity > 2 THEN
     SET discounted_price = discounted_price – 2.00;
     END  IF;
     UPDATE WIDGET
     SET widget_price = discounted_price WHERE widget_id = partid;
     Select * from widget;
     END
 |
1 row in set (0.00 sec)

{mospagebreak title=Alter Procedure and Conclusion}

Alter procedure

The alter procedure statement may be used to change the name of a stored procedure or change its other characteristics such as comments or security associated with it.

mysql> ALTER PROCEDURE PARTPRICE NAME partprice;
Query OK, 0 rows affected (0.00 sec)

Conclusion

There –- now you know  how to create a stored procedure — you are only limited by your imagination and the MySQL documentation. However, stored procedures in MySQL are still young and in the alpha stage.  So, don’t be surprised if you come across bugs; after all, this is only a sneak preview. If you do encounter bugs, please check at http://bugs.mysql.com. If your bug is not listed, please add it there for the benefit of others.

In this article, we learned how to create a simple stored procedure and run it from the command prompt. For more information on stored procedures in MySQL, visit the MySQL website at: http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html.

[gp-comments width="770" linklove="off" ]

chat