Using Stored procedure with mySQL and PHP

Writing external scripts to perform complex data handling is a tedious affair. The best way to automate tasks straightaway into the server is by using Stored Procedures. It is very useful to make them as flexible as possible, as it facilitates easy identification of any errors and can be used for executing a variety of tasks as well.

What are Stored Procedures?

Stored procedures are set of SQL commands that are stored in the database data server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. This also helps in decreasing the traffic in the networks and also reduces the CPU load.

There are many advantages of using stored procedures, which include:

The functionality is application and platform related.
Functionality has to be developed only once, and all applications can call the same commands.
Task execution becomes easier and less complicated.
Network Traffic reduced to a greater extent.
Centralization of all commands made possible, which is helpful for various applications that repeatedly call the same set of complicated commands.
Runs on any kind of environment.

MySQL Stored Procedures

For few years, Oracle and Microsoft SQL servers were having one upper hand over MySQL by having the facility to use the advantage of Stored Procedures. But this advantage has become a thing of the past now. With MySQL 5, you can use Stored Procedures the way you have been utilizing with other servers.

The syntax for using Stored Procedures is as follows:

CREATE

[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement

Application

MySQL Stored Procedures can be applied in absolutely any place. Right from complex applications to simple procedures, these stored procedures can be utilized in absolutely any place.

Few of the many places that MySQL Stored procedures can be used are:

When diverse client applications are structured using various languages in different platforms.
When security is of highest importance, like in financial institutions, the users and applications would have no direct access to the database tables. This provides excellent secured environment.
When very few database servers service the client machines, thereby providing efficient performance.

Though not as mature as Oracle, DB2 or the SQL Server, the MySQL Stored Procedures is definitely worth a try. If the structure of the database is the same, the same stored procedures can be used for all.

A simple example for MySQL Stored Procedure

To calculate the area of a circle with given radius R, the following commands can be given

delimiter //
create function Area (R double) returns double
deterministic
begin
declare A double;
set A = R * R * pi();
return A;
end
//
delimiter ;

And to call it from php code to display the area of a circle with radius 22cm,
<?
$rs_area = mysql_query(“select Area(22)”);
$area = mysql_result($rs_area,0,0);
echo “The area of the circle with radius 22cm is ”.$area.” sq.cm”;
?>

Recent Blog Posts