Stored Procedure with PHP & MYSQL

A stored procedure is a method to encapsulate repetitive tasks. They allow for variable declarations, flow control and other useful programming techniques.

Step 1 – Picking a Delimiter

The delimiter is the character or string of characters that you’ll use to tell the mySQL client that you’ve finished typing in an SQL statement. For ages, the delimiter has always been a semicolon. That, however, causes problems, because, in a stored procedure, one can have many statements, and each must end with a semicolon. In this tutorial I will use “//”
Step 2 – How to Work with a Stored Procedure
Creating a Stored Procedure

DELIMITER //
 
CREATE PROCEDURE `p2` ()
 
LANGUAGE SQL
 
DETERMINISTIC
 
SQL SECURITY DEFINER
 
COMMENT 'A procedure'
 
BEGIN
 
 SELECT 'Hello World !';
 
END//

The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code.

Stored procedure names are case insensitive, and you cannot create procedures with the same name. Inside a procedure body, you can’t put database-manipulation statements.

The four characteristics of a procedure are:

Language : For portability purposes; the default value is SQL.
Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER.
Comment : For documentation purposes; the default value is “”

Calling a Stored Procedure

To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory.
CALL stored_procedure_name (param1, param2, ....)
 
CALL procedure1(10 , 'string parameter' , @parameter_var);



Modify a Stored Procedure

MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.

Delete a Stored Procedure
DROP PROCEDURE IF EXISTS p2;



This is a simple command. The IF EXISTS clause prevents an error in case the procedure does not exist.
Step 3 – Parameters

Let’s examine how you can define parameters within a stored procedure.
 CREATE PROCEDURE proc1 ()


Parameter list is empty


CREATE PROCEDURE proc1 (IN varname DATA-TYPE)


One input parameter. The word IN is optional because parameters are IN (input) by default.


CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)

One output parameter.


CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)


One parameter which is both input and output.


Of course, you can define multiple parameters defined with different types. IN example


DELIMITER //
 
CREATE PROCEDURE `proc_IN` (IN var1 INT)
 
BEGIN
 
 SELECT var1 + 2 AS result;
 
END//



OUT example


DELIMITER //
 
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
 
BEGIN
 
 SET var1 = 'This is a test';
 
END //


INOUT example


DELIMITER //
 
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
 
BEGIN
 
 SET var1 = var1 * 2;
 
END //





Step 4 – Variables

The following step will teach you how to define variables, and store values inside a procedure. You must declare them explicitly at the start of the BEGIN/END block, along with their data types. Once you’ve declared a variable, you can use it anywhere that you could use a session variable, or literal, or column name.

Declare a variable using the following syntax:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;


Let’s declare a few variables:


DECLARE a, b INT DEFAULT 5;
 
DECLARE str VARCHAR(50);
 
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
 
DECLARE v1, v2, v3 TINYINT;




Working with variables

Once the variables have been declared, you can assign them values using the SET or SELECT command:
DELIMITER //
 
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
 
BEGIN
 
 DECLARE a, b INT DEFAULT 5;
 
 DECLARE str VARCHAR(50);
 
 DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
 
 DECLARE v1, v2, v3 TINYINT;
 
 INSERT INTO table1 VALUES (a);
 
 SET str = 'I am a string';
 
 SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
 
END //