MySQL Stored Procedures

The stored procedure is a set of statements that are defined to perform any task.

The stored procedure is the most prefered way to execute couple of queries in a single procedure.

The stored procedure can accept input and output parameters and perform task based on the input parameter and return the output.

CREATE PROCEDURE

The CREATE PROCEDURE statement is used to create a procedure.

Syntax for CREATE PROCEDURE

DELIMITER //
CREATE PROCEDURE stored_procedure_name ()
BEGIN
	query
	...
END //
DELIMITER ;

Example for CREATE PROCEDURE

DELIMITER //
CREATE PROCEDURE st_orderedcustomers()
BEGIN
select customer.name from 
customer
inner join orders
on customer.cust_id=orders.cust_id;
END //
DELIMITER ;

In the above stored procedure, st_orderedcustomers is the stored procedure name which retrieves the customer???s name who has ordered for any products.

Executing Stored Procedure

To execute the st_orderedcustomers stored procedure, use the following query

CALL st_orderedcustomers();

Example for Parameterized Procedure

DELIMITER //
CREATE PROCEDURE st_order_details (cus_id INT, max_amount INT)
BEGIN
SELECT Customers.cust_id, Customers.name, 
Orders.amount
FROM Customers
JOIN Orders
ON Customers.cust_id = Orders.cust_id
where Customers.customer_id = cus_id AND Orders.amount < max_amount;
END //
DELIMITER ;

Call the stored procedured with the following query

CALL st_order_details(4, 10000);

DROP STORED PROCEDURE

To drop the stored procedure using the following syntax

Syntax

DROP PROCEDURE procedure_name;

Example

DROP PROCEDURE st_order_details;