Stored Procedures in MySQL

The programmers and developers all over the world are celebrating the existence of MySQL 5.0 for a good reason. The version has finally paved the way for using stored procedures with this popular open source database. The stored procedures are hoarded at database server in the form of commands and SQL statements and could be invoked for any type of application. Thus, there is advantage for programmers to shed away the need of creating their own SQL; a tiresome job.

Without taking much of the time, let us understand the syntax for utilizing the procedures:

1.

2.
CREATE ProcedureCREATE [DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,…]])
3.

4.
[characteristic …] routine_body
5.

6.
IN the above syntax, the parameters are specified AS IN, OUT OR INOUT. However, IN case of CREATE FUNCTION displayed below, the only parameter allowed IS IN.
7.

8.
CREATE
9.

10.
[DEFINER = { user | CURRENT_USER }]
11.

12.
FUNCTION sp_name ([func_parameter[,…]])
13.

14.
RETURNS type
15.

16.
[characteristic …] routine_body

Return statement is included in the syntax for a function and it specifies the return type of the function. Routine body is included to define a valid SQL statement. The syntax contains characteristic to provide information regarding nature of data. This may include attributes like CONTAINS SQL, NO SQL, READS SQL DATA and MODIFIES SQL DATA.

Alter Procedure

This statement is used to modify or alter the characteristics possessed by a stored procedure. Here is the syntax:

1.

2.
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]

It offers you the advantage to bring in more than one change in the characteristics of stored procedures.

Drop Procedure

In order to remove a stored procedure, there is provision of DROP statement in MySQL database. The syntax for this takes the following shape:

1.

2.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

IF EXISTS is used to check the existence of specified procedure or function. It saves the statement from generating an error in case the procedure or function doesn’t exist.

Other Statements For Procedures

Apart from those mentioned above, there are other statements used for stored procedures. Here is the list o these statements:

* Show Create Procedure – It is used to return the text of a stored procedure, which was defined previously using Create syntax. In the similar manner, there is Show Create Function statement to accomplish this task for stored function.
* Show Procedure Status – It is used to return the characteristic of the specified stored procedure. These characteristics include name, type, creation date and so on. You may use Show Function Status also.
* CALL – It is used to invoke the stored procedure created using Create Procedure statement.
* Begin End – It is used to specify multiple statements for carrying out different types of operations and executions.
* Declare – It is used to help programmers in declaring and defining local variables, conditions, procedures and so on.
* SET – This statement is used to modify the values of local as well as global variables.

Also, there are condition statements like IF THEN ELSE, WHILE, CASE WHEN amongst others, which could be employed to operate these procedures.

Thus, MySQL stored procedures are tools for better performance, secure environment, providing ease of maintenance and also, for optimization.