.NET Information center: MySQL Stored procedure

Tuesday, December 27, 2011

MySQL Stored procedure

Stored Procedures

MySQL stored procedures can be applied easily in any place. There are many places that MySQL stored procedures can be used. Stored procedures are a set of sql commands that are stored in the database. Stored procedures are application and platform related and the task of execution becomes easier; less disorder. It also helps in decreasing the traffic in the networks and reduces the CPU work load. RDMBS supports recursive stored procedure but MySQL does not support it well.

There are many places MySQL stored procedures can be used :

    Where client applications are using various language in different platforms.
    If security is of highest importance, like in financial institutions,then users and applications would have no direct access to the database tables.
    This provides an excellent secured environment.
    The database servers service the client machines,providing efficient performance.

Now we are describing a example of a simple stored procedure which uses an OUT parameter. It uses the MySQL client delimiter command for changing the statement delimiter from ; to // till the procedure is being defined.

Example :

mysql> CREATE PROCEDURE Happy(OUT p1 INT)
-> SELECT COUNT(*) INTO p1 from persons;
Query OK, 0 rows affected (0.21 sec)

mysql> CALL Happy(@a);
Query OK, 1 rows affected (0.00 sec)

mysql> select @a;