Introduction:

MySQL is the most popular and an open source relational DBMS capable of working on several platforms. It has features such as multiple user creation and has access to various storage engines, it is backed by Oracle. A major feature of MySQL is that it supports the Stored Procedures which in turn can execute a set of queries/codes and may contain business logic. This blog will show the creation of a stored procedure and its execution.

Prerequisites:

• MySQL server
• SQL basic queries 

Steps:

1. Create Tables:

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

2. Create a Stored Procedure:

DELIMITER $$
CREATE PROCEDURE Test.get_sales
(
param_item_description VARCHAR(100)
)
BEGIN
SELECT item_description,SUM(sales_amount) AS sales_amount FROM items NATURAL
JOIN sales
WHERE item_description=param_item_description
GROUP BY item_description;
END;
$$
DELIMITER;

Note:  In our example, the beginning of the stored procedure starts by setting a Delimiter '$$'. The delimiter in MySQL statements by default is a semicolon (;). Hence, for instructing the engine of MySQL about start as well as the end of a stored procedure code, we need a different delimiter (in our case $$).

3. Stored Procedure Execution:

The stored procedures can be executed by following command-

CALL get_sales('Television');

Upon Execution the result is-

Item_description sales_amount
Television 3600.00

Thanks