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:
Pros
Cons
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.