MYSQL 5 has introduced the feature called stored procedure, which is a set of SQL statements which can perform repetitive task. The stored procedure can be called using applications like Java, PHP, etc... or other stored procedures. Stored procedure limits the user's direct access to data tables. It gives an interface and secure mechanism to manipulate data. With stored procedures, the repetition of code is avoided and the code block can be called whenever necessary. It allows variable declaration, parameter passing; return multiple values, flow statements.

Stored procedures are compiled when on need. Once it is compiled, it is put in cache. Every connection has a stored procedure cache. If the stored procedure is used multiple times in single connection the cache is accessed else, it works as query. If there are a number of stored procedures, the memory usage for every single connection gets increased. This in turn increases CPU usage. It is difficult to debug stored procedures. Store procedures have advantages and disadvantages. So their usage is decided by business requirements.

Creating stored procedures

Stored procedures are created using CREATE PROCEDURE or CREATE FUNCTION syntaxes. Create function creates User Defined Functions (UDF) which can be used as standard built-in functions. Stored procedure is created and used as in below example:

DELIMITER //

CREATE PROCEDURE getNames()

   LANGUAGE SQL 

   NOT DETERMINISTIC 

   SQL SECURITY DEFINER 

   COMMENT 'First procedure' 

   BEGIN

   SELECT name FROM tblAdmission; 

   END //

DELIMITER ;

Here, DELIMITER // statement sets the delimiter as '//' instead of ';'. The END // defines to MYSQL that the entire block from CREATE to END// is a stored procedure that has to be sent to server as a block instead of single statement. The DELIMITER; at the end, defines the delimiter back to ';' instead of '//'.

CREATE PROCEDURE getNames() creates a procedure with name 'getNames()'. If there are any parameters to be passed to the procedure they are put within the '()' of the procedure name.

LANGUAGE stand for the language used to write the subroutine. Only SQL is supported.

NOT DETERMINISTIC indicates the same result is not produced for the same input parameters. DETERMINISTIC is used for testing.

SQL SECURITY characteristics can be DEFINER or INVOKER. DEFINER has the all privileges as it defines the user who created the procedure. INVOKER defines the user who calls the procedure.

COMMENT is used for documentation purpose.

The SQL statements between BEGIN and END are executed. This procedure in the example retrieves the 'names' from the 'tblAdmission' and displays it.

Calling Stored Procedure

To call stored procedure the below syntax is used:

 CALL  STORED_PROCEDURE_NAME();

For example above it becomes:

CALL  getNames();