MySQL – Creating stored procedures

Published on Author gryzliLeave a comment

Stored Procedures in MySQL are the typical primer for great ideas and bad implementation. Even that I have stored procedures, because of their performance penalties, recently I had to write one. The procedure itself was very simple, but there were some catchy stages I would like to describe in case I need to write such again.

 

1| What is MySQL Stored Procedure

This is the mysql thing for “programming inside mysql” and yes you could write your own functions which are mostly limited to using SQL-derived statements. The functions could be given parameters, could return parameters, exit codes and so on.

If you need “more” on what are Stored Procedures and why do they exists, I’m not the right guy to tell you, so get your ticket to dev.mysql.com or something more eligible resource

 

2| Creating Stored Procedure

The first step you will encounter is to create the procedure.

Comments in SQL and Stored Procedures could be one of these:

/* ….some commented multilined text …..*/ 

— This is one line comment

 

My test procedure (named new_schedule) looked like this:

( I have added comments describing each  row)

 

 

 

From now on, every code example I give is supposed to be inside stored procedure definition, so I won’t add again and again the procedure definitions

3| Variable declarations inside Stored Procedure

As with most of the programming languages, here you could also declare variables. Stored procedures in mysql have “strict type” declarations, which means that you must also mention the type of the variable you are declaring.

[Important Note] It is very important to make sure your variable declarations are at the beginning of the stored procedure. If you try to declare variable later on in the procedure, mysql will throw you some nasty and hard to debug errors.

[/ Important Note]

 

Some example declarations:

Whatever variable you plan to use inside the procedure, must be firstly defined in the beginning. Later you could assign it a value.

[Important Note]

If you have any cursor or handler declarations ( which are used for looping), you must always use the following declarations order:

  • First must be variable/conditional delcarations
  • Second are cursor declarations
  • Third are handler declarations

[/ Important Note]

 

4| Using IF statements inside Stored Procedure

The next “cool” thing is you have “Flow control statements” or more precisely:

CASE  , IF/THEN/ELSE and even WHILE and LOOP statements.

I will give example for using the IF/THEN/ELSE and also iterating through a LOOP

 

5| Iterate over SELECT result in a LOOP inside Stored Procedure

One of the thing you will almost 100% need is doing a LOOP. Usually this is when you want to process all result rows from a given select.

Here is one way to use the internal LOOP keyword inside stored procecdure;

First we will declare CURSOR variable which will give us the chance to iterate over result set by using the keyword FETCH . FETCH returns one value at a time.

 

We will also use HANDLER declaration which is used to determine if we have empty result set returned form the SELECT and so don’t iterate into the loop.

Here is the example:

 

6| Exiting a Stored Procedure and Throwing Error Message

Sometimes you may need to exit a procedure in such a way that it will return usable message to the CALLER. The caller could be you if you manually execute the procedure by using CALL() or another stored procedure.

You can exit the procedure and return message by using the “SIGNAL” keyword.

More information on SIGNALs could be found here:

https://dev.mysql.com/doc/refman/5.5/en/signal.html

 

Here is a simple example I was using in my procedure to return an error:

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha * Time limit is exhausted. Please reload CAPTCHA.