MYSQL STORED PROCEDURE

What is Stored Procedure
      It is a segment of declarative SQL code, it is stored in the database catalog. It can be done by a program, trigger or even another stored procedure

What are the advantages of stored procedure?

1. It increase performance of application
2. It reduces the traffic between application and database.
3. It is reusable and transparent to any application which will be using or use this
4. Last but not the least it is secured.



What are the disadvantages of stored procedure?

1. It makes the database server high load  in both memory and processors.
2. It only contains SQL declarative statements
3. Difficult to debug.
4. Not easy to write and maintain

HOW TO CREATE STORED PROCEDURE:

DELIMITER //

                        CREATE PROCEDURE ShEmployees()

                        BEGIN

                        SELECT * FROM employee;

                        END //

                        DELIMITER ;

 
CALLING THE STORED PROCEDURE:
SYNTAX:

CALL STORED_PROCEDURE_NAME();

Example:
CALL ShEmployees(); 

STORED PROCEDURE PARAMETERS:

     By the use of parameters it makes  the stored procedure more flexible and useful.

3 modes:

      IN - default mode, means it can be passed into stored procedure

      OUT - it indicates that stored  procedure can change this parameter and pass back to the calling program.

     INOUT - combination of IN and OUT mode; you can pass parameter into stored procedure and get it back with the new value from calling program

 SYNTAX:

•      MODE param_name param_type(param_size)