MYSQL Stored Procedures

Introduction to SQL 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 application.

•      Stored procedure reduces the traffic between application and database .

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

•      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.

•      Stored procedure is difficult to debug.

•      Store procedure is not easy to write and maintain.

Getting Started with MySQL Stored Procedures

Creating the first stored procedure

                        Sample Procedure:

                        DELIMITER //

                        CREATE PROCEDURE Showdepartmenr()

                        BEGIN

                        SELECT * FROM department;

                        END //

                        DELIMITER ;

                         

Getting Started with MySQL Stored Procedures(Cont…)

Calling the stored procedure

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

                        CALL STORED_PROCEDURE_NAME();

Sample:

                        CALL Showdepartment();

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 stored procedure does not change 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 stored procedure and get it back with the new value from calling program.

The syntax of defining a parameter in 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’);

CALL GetEmployeeByGender(‘F’);

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();