MYSQL Stored Procedures With Command Line

Creating MYSQL Stored Procedures With The Command Line

Stored procedures require a different set of methods from typical mysql queries, but, there are only a few which need to be implemented. For example, you need to create a procedure with 'CREATE PROCEDURE', you use 'BEGIN' and 'END' around the declared variables and mysql query and you declare and set variables. Finally, you use a delimiter so that mysql will run all of the code; rather than an execute when it sees a semi-colon(;). The examples below show how to make simple stored procedures.

Why use stored procedures?

Using stored procedures allows a programmer to create custom mysql queries that can be used throughout variious scripts without having to hard code each and every time time. Essentially, using stored procedures can reduce redundancy.

Example #1

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

Example #2

DELIMITER //
CREATE PROCEDURE second_procedure()
BEGIN
DECLARE my_variable INT DEFAULT 0;
SET my_variable = (SELECT COUNT(*) AS cnt FROM at_admins)
select my_variable;
END //
mysql> DELIMITER ;

Example #3

DELIMITER //
CREATE PROCEDURE third_procedure()
BEGIN
DECLARE my_variable varchar(255) DEFAULT 0;
SET my_variable = (SELECT login FROM at_admins WHERE login='admin');
select my_variable;
END //
mysql> DELIMITER ;

The simple code below shows how you can call a stored procedure from a php script.

$command = "CALL new_procedure()";
$result = mysqli_query($db, $command);