Welcome To Tutorial Info

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:

Syntax for Stored Procedures

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”;

?>