MySQL Stored Procedure

Stored Procedure

1)Stored Procedure are a set of declarative SQL statements which when created are compiled and stored in database.The main reason for their creation is that they are reusable. Stored procedure work differentlty in MySQL, in this Stored procedures are compiled when they are on high demand i.e when they are used frequently. After compiling frequently used stored procedure, MySQL puts compiled version into a cache. For every single connection separate cache of stored procedure is maintained. If in a single connection stored procedure is used frequently then compiled version of stored procedure is used otherwise it works like a query.

2)Stored procedures can be invoked by other stored procedure, triggers or application like java, python etc. A stored procedure can call itself called recursive stored procedure.

3)The traffic between application and database server is reduced because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.

4)The developers dont have to develop functions that are already supported in stored procedures as database interface is visible to all applications.

5)Stored procedure are executed explicitly using by using CALL command.

Syntax for creating procedure

CREATE PROCEDURE PROCEDURE-NAME()
   BEGIN

   //A set of declarative SQL statements

   END 

This command will be executed and stored procedure will be stored in database.

Declaring a variable in Stored Procedure

DECLARE variable-name datatype(size) DEFAULT default-value;

Assigning a value to variable:

SET variable-name = value;

MySQL stored procedure parameters:

Syntax for defining a parameter in the stored procedures:

MODE parameter-name parameter-type(parameter-size)

MODE ARE OF THREE TYPES:

1)IN: IN mode is the default mode. IN parameter in stored procedure causes calling program to pass an argument to stored procedure and its value is protected i.e stored procedure work on copy of IN parameter value, it can be changed inside stored procedure but original value is retained when stored procedure ends.

2)OUT:OUT value can be changed inside Stored procedure but is passed back to calling program. When stored procedure starts initial value of OUT parameter cannot be accessed.

3)INOUT: Combination of IN and OUT parameter thereby combining their functionality.

Example of Stored Procedure:

CREATE PROCEDURE GetEmployeeByDepartment(IN departmentName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM Employee
 WHERE department = departmentName;
 END

Explanation:

The departmentName is the IN parameter of the stored procedure. Inside the stored procedure, we select all employees that are in particular department specified by the departmentName parameter. When calling this stored procedure an IN parameter is passed.

Stored Procedure is called as:

CALL  GetEmployeeByDepartment('sales')