An Introduction to Stored Procedures in MySQL 5

MySQL 5 introduced a plenty of new features – stored procedures being one of the most significant. In this tutorial, we will cover the basics of stored procedures and its significance in web programming.

Introduction

“A stored procedure is a set of declarative SQL statements that can be stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.”

A stored procedure, which calls itself, is recursive stored procedure. Almost all RDMBS supports recursive stored procedure but MySQL does not support it well.

Stored routines require the proc table in the mysql database. This table is created during the MySQL 5.0 installation procedure. If you are upgrading to MySQL 5.0 from an earlier version, be sure to update your grant tables to make sure that the proc table exists.

Stored routines can be particularly useful in certain situations:

1. When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
2. When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

Pros

1. Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.
2. Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.
3. Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported in stored procedure in all programs.
4. Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table. If you can only access the data using the stored procedures defined, no one else can execute a DELETE SQL statement and erase your data.
5. Share logic with other applications. Stored procedures encapsulate functionality; this ensures that data access and manipulation are coherent between different applications.

Cons

1. Stored procedure make the database server high load in both memory for and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the role of it.
2. Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++…
3. You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.
4. Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase. You’ll need to learn the syntax of MySQL statements in order to write stored procedures.

Creating a Stored Procedure

CREATE PROCEDURE command is used for creating a stored procedure.

DELIMITER $$

CREATE PROCEDURE `proc1`()

BEGIN

SELECT ‘Hello World !’;

END$$

Here $$ is the delimiter used to tell the MySQL client that you’ve finished typing in an SQL statement, you can use the delimiter // instead of  $$.

Calling a Stored Procedure

CALL command is used for executing a stored procedure.

Syntax: CALL proc1 (param1, param2, ….)

Delete a Stored Procedure

DROP PROCEDURE command is used for deleting a stored procedure.

DROP PROCEDURE IF EXISTS proc1;

This command will execute only if the procedure ‘proc1’ exist in database.

Comments in Stored Procedure

— is used for comments in stored procedure.

Example:

— Declaring the variable age with default value 25

DECLARE age TINYINT(3) DEFAULT 25;

Passing Parameters to Stored Procedure

1) CREATE PROCEDURE proc1 (IN varname DATA-TYPE)

Here one input parameter is passed to stored procedure. The word IN is optional because parameters are IN (input) by default.

CREATE PROCEDURE `proc1` (IN var1 INT)

BEGIN

SELECT var1 + 2 AS result;

END

2) CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)

Here one output parameter is get from stored procedure as result.

CREATE PROCEDURE `proc1` (OUT var1 VARCHAR(100))

BEGIN

SET var1 = ‘Hello World !’;

END

3) CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)

Here one parameter ‘var1’ is used which is both input and output.

CREATE PROCEDURE `proc1` (INOUT var1 INT)

BEGIN

SET var1 = var1 * 2;

END

Working with variables

DECLARE command is used for declaring a variable in stored procedure

Syntax: DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Example:

DELIMITER $$

CREATE PROCEDURE `proc1` (IN name VARCHAR(100))

BEGIN

DECLARE type VARCHAR(50);

DECLARE age TINYINT(3) DEFAULT 25;

SET type = ‘Employees’;

INSERT INTO persons (`person_name`, `person_age`, `person_type`) VALUES (age, name, type);

END $$

 

CALL proc1(‘xxx’);

CALL proc1(‘yyy’);

Flow Control Structures

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE and REPEAT constructs for flow control within stored programs.

IF statement

DELIMITER $$

CREATE PROCEDURE `proc1` (IN name VARCHAR(100), IN age TINYINT(3))

BEGIN

DECLARE type VARCHAR(50);

IF age <= 10 THEN

SET type = ‘Child’;

ELSE

SET type = ‘Adult’;

END IF;

INSERT INTO persons (`person_name`, `person_age`, `person_gender`, `person_type`) VALUES (age, name, gender, type);

END $$

CALL proc1(‘xxx’ , 25);

CALL proc1(‘yyy’ , 10);

CASE statement

DELIMITER $$

CREATE PROCEDURE `proc1` (IN name VARCHAR(100), IN age TINYINT(3), IN gender VARCHAR(6))

BEGIN

DECLARE salutation VARCHAR(10);

IF age <= 10 THEN

SET salutation = ‘Master’;

ELSE

CASE gender

WHEN ‘male’ THEN

SET salutation = ‘Mr.’;

WHEN ‘female’ THEN

SET salutation = ‘Miss.’;

ELSE

SET salutation = ”;

END CASE;

END IF;

INSERT INTO persons (`person_name`, `person_age`, `person_gender`, `person_salutation`) VALUES (age, name, gender, salutation);

END $$

 

CALL proc1(‘xxx’ , 25, ‘male’);

CALL proc1(‘yyy’ , 10, ‘female’);

WHILE statement

DELIMITER $$ 

CREATE PROCEDURE `proc1` (IN param1 INT)

BEGIN

DECLARE variable1, variable2 INT;

SET variable1 = 0; 

WHILE variable1 < param1 DO

INSERT INTO table1 VALUES (param1);

SELECT COUNT(*) INTO variable2 FROM table1;

SET variable1 = variable1 + 1;

END WHILE;

END $$

Cursors

To handle a result set inside a stored procedure, you can use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

DECLARE cursor-name CURSOR FOR SELECT …;

/*Declare and populate the cursor with a SELECT statement */

DECLARE CONTINUE HANDLER FOR NOT FOUND

/*Specify what to do when no more records found*/

OPEN cursor-name;

/*Open cursor for use*/

FETCH cursor-name INTO variable [, variable];

/*Assign variables with the current column values*/

CLOSE cursor-name;

/*Close cursor after use*/

DELIMITER $$

CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))

BEGIN

DECLARE v_finished INTEGER DEFAULT 0;

DECLARE v_email varchar(100) DEFAULT “”;

— declare cursor for employee email

DECLARE email_cursor CURSOR FOR SELECT email FROM employees;

— declare NOT FOUND handler

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

OPEN email_cursor;

get_email: LOOP

FETCH email_cursor INTO v_email;

IF v_finished = 1 THEN

LEAVE get_email;

END IF;

— build email list

SET email_list = CONCAT(v_email,”;”,email_list);

END LOOP get_email;

CLOSE email_cursor;

END$$

Conclusion

In this lesson, we covered the fundamentals of stored procedures and some specific properties pertaining to them. This chapter seems to be helpful to gain the basic idea about stored procedures, you should continue your studies and learn more about stored procedures.