Stored Procedures in MySQL

Stored Procedures in MySQL


The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.
Stored procedures can have IN, OUT & INOUT parameters, depending on the MySQL version. The mysqli interface has no special notion for the different kinds of parameters.

Usually stored procedures are not used in the PHP Development, but still its a useful option for reducing the query writing practice.

Some examples of stored procedures with different types are as follow.

IN parameter

To create a stored procedure with in parameter you can write a query like this.

CREATE PROCEDURE p(IN id_val INT)
BEGIN
INSERT INTO test(id) VALUES(id_val);
END;

Now to call the stored procedures you can do something like this.

CALL p(1);

OUT parameter

To create a store procedure with inout/out parameter you can write a query like as following.

CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END


Now to call the stored procedures you can do something like this.

CALL CountOrderByStatus('Shipped',@total);

if you want to execute a php code, you can write like this

<?php
$result=mysql_query("CALL CountOrderByStatus('Shipped',@total)");
?>

INOUT parameter

To create a store procedure with inout/out parameter you can write a query like as following,
INOUT exactly works like the OUT parameter

CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
 SET count = count + inc;
END;

Now to call the stored procedures you can do something like this.

CALL set_counter(@counter,1);


That's it, its damn easy to go on with.