MYSQL Stored Procedures


MYSQL Stored Procedures With phpMyAdmin

Creating stored procedures in mysql can be done with phpMyAdmin or the mysql console. Obviously, this particular example will be show how to make a stored procedure with phpMyAdmin.

The code below will create a new stored procedure called new_procedure. The 'CREATE PROCEDURE' statement will be on the first line. The second line contains the 'BEGIN'. Then, a variable is declared using the 'DECLARE' statement. The declare statement includes the datatype and size, and a default value. In this case, we declare an integer with a default value of 0. The 'SET' statement gives the newly declared variable a value of 10. the 'select my_variable' is a typical mysql query.Finally, 'END' is used to complete the task. It is very important that the delimiter is blank before selecting 'Go'. After everything looks good, select 'Go'.

 
Here are 3 example sets of code for stored procedures.

CREATE PROCEDURE new_procedure()
BEGIN
DECLARE my_variable INT DEFAULT 0;
SET my_variable = 10;
select my_variable;
END;

CREATE PROCEDURE second_procedure()
BEGIN
DECLARE my_variable INT DEFAULT 0;
SET my_variable = (SELECT COUNT(*) AS cnt FROM calendar)
select my_variable;
END;

CREATE PROCEDURE third_procedure()
BEGIN
DECLARE my_variable varchar(255) DEFAULT 0;
SET my_variable = (SELECT title FROM calendar WHERE id='3');
select my_variable;
END;

To call a stored procedure, the statement call new_procedure() is implemented.

 
 

To view the stored procedure,

SHOW CREATE PROCEDURE new_procedure;  Note: You may want to select 'Print view' to see the details.