Stored Procedures in MySQL

Stored procedure is a segment of declarative SQL statements stored inside the MySQL Server. To define it other way, we can save (one or set) queries within stored procedure for future use.
For example, we have a SELECT query that returns rows based on the condition
SELECT * FROM customers WHERE city = ‘Bangalore’ ORDER BY firstName;
The first time you invoke a stored procedure, MySQL looks up for the name in the database catalog, compiles the stored procedure’s code, place it in a memory area known as a cache, and execute the stored procedure.
If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.
A stored procedure can have parameters so you can pass values to it and get the result back.
Advantages

• Reduce network traffic
• Centralize business logic
• More secure

Disadvantages

• Resource Usages
• Troubleshooting
• Maintenance

Creating Stored Procedures

If we want to save this query in the server for later, can use stored procedure as below

• Change the default delimiter character to some other character
• Create procedure with name
• Begin statement
• Define set of queries
• End statement
• Change the delimiter to default.

DELIMITER $$
CREATE PROCEDURE GetCustomers()
BEGIN
	SELECT * FROM customers WHERE city = ‘Bangalore’ ORDER BY firstName;
END $$
DELIMITER ; 

Once the stored procedure is created, can invoke it by using the CALL statement.

CALL GetCustomers();

This statement also returns the same result as the query.

To elaborate, we will start with DELIMITER.

In MYSQL, we usually use semicolon ( ; ) as a delimiter to separate and execute the statements. A stored procedure contains multiple statements separated by ( ; ). To compile the whole stored procedure as a single compound statement, we need to temporarily change the DELIMITER from the regular semicolon ( ; ) to another character such as $$ or //

The DELIMITER character may consist of a single or multiple characters. However, we should avoid using the backslash ( \ ) because it’s the escape character in MySQL.

DELIMITER $$ (or) DELIMITER //

We can check the stored procedures by opening the “Stored Procedures” node in the MySQL workbench or in phpMyAdmin

We can also use SHOW PROCEDURE STATUS; in the command line to get the list of stored procedures in the database.

Stored Procedure Parameters

With parameters, we can make stored procedures more useful and reusable. A parameter in a stored procedure has one of three modes: IN, OUT, or INOUT.

IN Parameter

IN is the default mode. When we define this parameter in a stored procedure, the calling program has to pass the parameter. The value of IN parameter is protected. It means, even you change the value of IN inside the stored procedure, its original value is unchanged at the end. In other words, the stored procedure only works on the copy of the IN parameter.

CREATE PROCEDURE GetUserByName(IN firstName VARCHAR(255))

In this example, the firstName is the IN parameter of the stored procedure. And we should call the procedure as mentioned below. Missing the parameter will throw an error.

CALL GetUserByName(‘Ravi’);

OUT Parameter

OUT parameter can be used to read the result value from the stored procedure executed.

DELIMITER $$
CREATE PROCEDURE GetOrderCount (OUT total INT) 
BEGIN
	SELECT COUNT(OrderId) INTO total FROM orders 
END $$
DELIMITER //

The above procedure can be called as below

CALL GetOrderCount (@total);
SELECT @total;

INOUT parameter

In this method of INOUT, the parameter works as input value and the output of the procedure.

CREATE PROCEDURE SetCounter(INOUT counter INT)

Variables in Stored Procedures

We can declare and use variables inside the stored procedures during execution. We can change variable to hold the immediate results as they are local.

The statement for declaring a variable is mentioned below

DECLARE variable_name datatype(size) [DEFAULT default_value];
Ex: DECLARE totalSale DEC(10,2) DEFAULT 0.0;

To set the value after the variable is declared,

SET variable_name = value; 

To use the value inside the stored procedure,

SELECT COUNT(*) INTO variable_name  FROM products;

The scope of the variable will be out when the END statement of procedure reaches.

Altering the Stored Procedures

Fortunately, MySQL does not have any statement that allows you to directly modify the parameters or the body of the procedure.

To make such changes, you must drop and re-create the stored procedure using DROP PROCEDURE & CREATE PROCEDURE statements.

Deleting the Stored Procedures

The DROP PROCEDURE statement deletes a stored procedure created by the CREATE PROCEDURE statement.

DROP PROCEDURE [IF EXISTS] GetCustomers;