Beginning With MySQL Stored Procedure Programming

While doing database programming, we mainly focus on SQL statements that do ‘select’,’insert’,’update’,’delete’ operations with some conditions ‘where’,’like’ and some orders ‘group by’, ‘order by’. For a little complex goal, use join operations. MYSQL database is a very popular name in web applications development, specially for small and medium scale applications. Moreover, PHP has a good support in connecting/executing queries, fetch results. But, until MYSQL 5 releases, it didn’t support stored procedures , which is one of the major important features in RDBMS. But, as traditionally as developers already used raw query writing and execute them from PHP, still today, most of them are using same ways, although their server is upgraded to >= mysql 5. However ,we should use it for our own benefits if our server supports. In this tutorial, I will try to give a very basic idea to start using MySQL stored procedure and simple code samples also.

What is STORED PROCEDURE:

As its name imply, it is one kind of procedure/method/function that is stored within the database systems and contains a set of sql statements, which can be executed consequently one after another in a single call.

Creating Stored Procedure In PHPMyAdmin

Most of the developers use MySQL database on web applications and in that sense, PHPMyAdmin is their best database management tools. To create a stored procedure in PHPMyAdmin is quite easy. Simply go to the ‘query’ tab. Write the following simple statements:

DELIMITER //
CREATE PROCEDURE my_procedure                /* Procedure Name*/
(IN id INTEGER)                    /* parameters passed to the procedure */
BEGIN                                      /* start body of the procedure*/
  DECLARE name CHAR(10);                /* A simple variable those can be used in  stored procedure */
  IF id%2=0 THEN                    /* Simple conditional block */
    SET name = 'even';                   /* assign statement */
  ELSE
    SET name = 'odd';
  END IF;
  INSERT INTO tbl_numbers VALUES (id,name);    /*  sql insert statement */
END                                       /* end of procedure body */
// DELIMITER ;

Now, if you simply run these by clicking ‘go’, it will execute and create an stored procedure on your database. Remember to use the delimiter part. This is important, helps converting ‘;’ to ‘//’ so that multiple SQL statements can be executed. This will be shown as ‘Routines’ under the structure tab on database root page(where no table is selected). To edit/view the query of the procedure , click ‘structure’ column link next to the routine name.

To execute these procedures are also quite easy. Here are simple PHP code samples to execute the above mentioned stored procedure:

$id = 1;
$result = mysql_query('call my_procedure($id)');

So, you can easily assuming, how it can reduce complexity of writing raw SQL inside your server script and gives more flexibility in re-usability. also, if you are working on an existing PHP project that utilizes stored procedure, you won’t have to find which server script functions is doing that kind of works. You can simply find them by checking the routines on PHPMyAdmin.

Also just be careful about the following terms in using stored procedures:

• Use a meaningful name of the procedure which indicates the internal functionality transparently so others can understand just by checking the procedure name.
• Use proper parameter types and careful they are called.
• It is not possible to debug  MySQL stored procedures(as far i know, applies to most of other DBMS also, special case: there is debugger for SQL server on the recent 2008 version), so you have to write procedures in a clean way to be easily adaptable so that others can catch an potential error/enhancement points easily.

I have also found some good tutorials on various parts of MySQL stored procedures on MySQL tutorials website. You can go through them also for more detailed and deep knowledge. Besides, don’t forget to follow the database best practices as well. You can also ask if you have any, here by commenting. happy coding 🙂 .