MySQL Stored Procedures and PHP can prove to be
a great combination! This article explains about MySQL Stored
Procedures in detail including its Syntax, Application areas and an
example that uses PHP coding along with MySQL SP.
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”;
?>
|
