MySQL Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. But there is always a question in our mind that what are stored procedures and why we use them. When we can use queries then what is the advantages of stored procedures?

The answer is very simple.

A stored procedure is simply a block of code that is stored on the database server.

Advantages of stored procedures?

• When we are working with stored procedures, we kept our logic on database server in compiled form. We need not to compile our queries every time. When we user queries in our code, then every time the query get compiled and after that the query return the results to the application. but when we use stored procedure, we compile the query only once and can call our stored procedure any time.
• Stored procedures can reduce network traffic. When we have to do some complex and repetitive task on the results and again we have to again apply some other logic to get more result then it will be good to use stored procedures.

A simple example

Almost any valid SQL can be used inside a stored procedure. Let’s set up a basic stored procedure first. This one will simply say ‘Hello World’.

mysql> CREATE PROCEDURE first_sp() SELECT ‘Hello World’;

Query OK, 0 rows affected (0.00 sec)

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

mysql> CALL first_sp();

*************************** 1. row ***************************

first_sp: Hello World

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:

• IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
• OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
• INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

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. SinceIN 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);

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.