MYSQL Stored Procedures

                A stored procedure is a procedure like a subprogram in a regular computing language that is stored in the database.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 ShowStudents()

                                BEGIN

                                SELECT * FROM students;

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

                If you run the command above you will get all employees in the students 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();

TRIGGERS

•       SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs.

Trigger implementation in MySQL

•       While trigger is implemented in MySQL has all features in standard SQL but there are some restrictions you should be aware.

                Some of the restrictions:

·         It is not allowed to create a trigger for views or temporary table.

·         It is not allowed to use transaction in a trigger.

·         Return statement is disallowed in a trigger.

              Creating a trigger for a database table causes the query cache invalidated.

                *Query cache allows you to store the result of query and corresponding select statement.

                In the next time, when the same select statement comes to the database server, the database server will use the result which stored in the memory instead of parsing and executing the query again.

·         All trigger for a database table must have unique name.

                                *It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. 

                 To create the trigger, you can use the following naming convention:

                                Syntax:

                                                (BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)