MYSQL Stored Procedures

MYSQL Stored Procedures

                DEFINITION:

                A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

Stored Procedures Advantages

•       Stored procedure increases performance of the application.

•       Stored procedure reduces the traffic between application and database .

•       The stored procedure is reusable and transparent to any application which wants to use it.

•       The stored procedure is secured.

Stored Procedures Disadvantages

•       Stored procedures make the database server high load in both memory and processors.

•       Stored procedure only contains SQL declarative statements.

•       The stored procedure is difficult to debug.

•       The stored procedure is not easy to write and maintain.

Getting Started with MySQL Stored Procedures

                Creating the first stored procedure

                                Sample Procedure:

                                DELIMITER //

                                CREATE PROCEDURE ShowEmployees()

                                BEGIN

                                SELECT * FROM employees;

                                END //

                                DELIMITER ;

Calling the stored procedure

In order to invoke a stored procedure, we use the following SQL command:

                                CALL STORED_PROCEDURE_NAME();

Sample:

                                CALL ShowEmployees();

If you run the command above you will get all employees in the employees database table.

STORED PROCEDURE PARAMETERS

•                       Parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT and INOUT.

•       IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any modification inside a stored procedure does not change a parameter.

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

•       INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into a stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in a stored procedure is as follows:

•       MODE param_name param_type(param_size)

SAMPLE STORED PROCEDURE WITH PARAMETERS

                DELIMITER //

CREATE PROCEDURE GetEmployeeByGender(IN gender varchar(1))

BEGIN

SELECT CONCAT(fname,' ', minit, ' ', lname) as fullname, bdate, address FROM employee WHERE sex = gender;

END //

DELIMITER ;

CALL GetEmployeeByGender(‘M’);

HOW TO SHOW STORED PROCEDURES

                For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

                                SELECT ROUTINE_TYPE, ROUTINE_NAME
                                FROM INFORMATION_SCHEMA.ROUTINES
                                WHERE ROUTINE_SCHEMA='dbname';

To delete a stored procedure, use the following statement:

DROP PROCEDURE ProcedureName();