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;

Alter Procedure

To create the procedure then we used the CREATE PROCEDURE statement and altering the procedure we used this statement. Alter Procedure statement is used to change access permissions that secure by the procedure and ALTER PROCEDURE needs the use of the same encryption and recompile option as the original CREATE PROCEDURE command. MySQL automatically grants the ALTER ROUTINE and EXECUTE opportunity to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable.

The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time. ALTER PROCEDURE statement can be used for renaming the stored procedure  and for changing it characteristics also. We can specify the more than one changes also in an ALTER PROCEDURE statement. But for this according to required the ALTER ROUTINE privilege.

Synatax :

ALTER {PROCEDURE} {proc_name } [characteristic ...] characteristic:
 SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string'

Example :

mysql> ALTER PROCEDURE happy SQL SECURITY DEFINER;

Note : The ALTER PROCEDURE Statement we can change only the characteristics and if we want to change in statement list then we have to DROP the procedure and CREATE again.

CALL Statement Syntax 

The CALL statement is used to call a procedure, which has been defined previously. CALL can return the values to its caller through its parameters that are declared as OUT or INOUT parameters. This statement is also used to returns the number of rows affected that a client program can obtain at the SQL level by calling the ROW_COUNT().

Syntax :

  CALL p_name([parameter[,...]])

Example : In the following example we show that the use of CALL statement.

mysql>CREATE PROCEDURE abc(OUT a VARCHAR(20),OUT a1 VARCHAR(20),IN a2 INT)
       > SELECT FirstName,City INTO a,a1 FROM persons WHERE P_id=a2;


mysql> CALL abc(@NAME,@city,2);

mysql> select @name,@city;

DROP PROCEDURE 

DROP PROCEDURE  Statement is used to drop a Procedure . But for dropping them you must have the ALTER ROUTINE privilege. If IF NOT EXISTS clause is available then its prevents you from occurring an error when the procedure does not exist its produced only a warning.

Syntax :   DROP {PROCEDURE } [IF EXISTS] {proc_name };

Examples : In this example we shows that a syntax of Dropping procedure  if it exists in our database :

mysql> DROP PROCEDURE IF EXISTS abc;