MariaDB - Stored Procedures

A procedure (also known as stored procedure) is a collection of SQL statements wrapped within the CREATE PROCEDURE statement. A procedure always contains a name, parameter lists, and SQL statements. It may also contain a conditional statement like IF or CASE or the Loops. A procedure can be invoked by using procedures, other procedures and applications.

Stored Procedure Features

• Stored Procedure increases the performance of the applications. Once stored procedures are created, they are compiled and stored in the database.
• Stored procedure reduces the traffic between application and database server. Because the application has to send only the stored procedure's name and parameters instead of sending multiple SQL statements.
• The stored procedure are reusable. A business logic can be implemented within a stored procedure which can be used by applications multiple times, or can be used by different modules of an application. This makes the database more consistent. If any change is required, it can be made in the stored procedure only.
• The stored procedures are more secure than the AdHoc queries. The permission can be granted to the user to execute the stored procedure without giving permission to the tables used in the stored procedure. The stored procedure helps to prevent the database from SQL Injection.

CREATE PROCEDURE

The CREATE PROCEDURE statement is used to create a stored procedure. The CREATE PROCEDURE statement requires the CREATE ROUTINE privilege for it.

By default, a stored routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.procedure_name at the time of creation.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case-sensitive.

The stored procedure can also execute another stored procedure or a function that modularizes the code.

The syntax of using CREATE PROCEDURE statement in MariaDB is given below:

DELIMITER &&  
CREATE 
  [OR REPLACE] 
  PROCEDURE procedure_name ([[ IN | OUT | INOUT ] parameter datatype [,...] ])

BEGIN

  declaration_section

  executable_section

END &&  
DELIMITER;  

Parameters

To invoke a stored procedure, the CALL statement is used. See the syntax below:

CALL procedure_name(parameter(s));

Create Procedure without Parameter

Consider a database table called Employee with the following records:

+-------+--------+-----------+-----+--------+
| EmpID | Name   | City      | Age | Salary |
+-------+--------+-----------+-----+--------+
| 1     | John   | London    | 25  | 3000   |
| 2     | Marry  | New York  | 24  | 2750   |
| 3     | Jo     | Paris     | 27  | 2800   |
| 4     | Kim    | Amsterdam | 30  | 3100   |
| 5     | Ramesh | New Delhi | 28  | 3000   |
| 6     | Huang  | Beijing   | 28  | 2800   |
+-------+--------+-----------+-----+--------+

To fetch all records of this table where Salary is greater than or equal to 3000 and count all the table rows, the following code can be used which creates a procedure named high_salary_employees:

DELIMITER &&  
CREATE PROCEDURE high_salary_employees()

BEGIN
  SELECT * FROM Employee WHERE Salary >= 3000;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;  
END &&  
DELIMITER ;  

After successful execution, the procedure can be called as follows:

CALL high_salary_employees();  

This will produce the result as shown below:

+-------+--------+-----------+-----+--------+
| EmpID | Name   | City      | Age | Salary |
+-------+--------+-----------+-----+--------+
| 1     | John   | London    | 25  | 3000   |
| 4     | Kim    | Amsterdam | 30  | 3100   |
| 5     | Ramesh | New Delhi | 28  | 3000   |
+-------+--------+-----------+-----+--------+
3 rows in set (0.00 sec)

+---------------+
| TotalEmployee |
+---------------+
| 6             |
+---------------+
1 row in set (0.02 sec)

Create Procedure with IN Parameter

In this procedure, the IN parameter is used with parameter named 'var1' of integer type to take the value from user. Its body part fetches the records from the table using a SELECT statement and returns only those rows that is provided by user. It also returns the total number of rows of the specified table.

DELIMITER &&  
CREATE PROCEDURE get_employees(IN var1 INT)

BEGIN
  SELECT * FROM Employee LIMIT var1;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;  
END &&  
DELIMITER ;  

After successful execution, the procedure can be called as follows:

CALL get_employees(3);  

This will produce the result as shown below:

+-------+-------+----------+-----+--------+
| EmpID | Name  | City     | Age | Salary |
+-------+-------+----------+-----+--------+
| 1     | John  | London   | 25  | 3000   |
| 2     | Marry | New York | 24  | 2750   |
| 3     | Jo    | Paris    | 27  | 2800   |
+-------+-------+----------+-----+--------+
3 rows in set (0.00 sec)

+---------------+
| TotalEmployee |
+---------------+
| 6             |
+---------------+
1 row in set (0.02 sec)

Create Procedure with OUT Parameter

In this procedure, the OUT parameter is used with parameter named 'maxsalary' of integer type. Its body part fetches the maximum salary from the table using a MAX() function.

DELIMITER &&  
CREATE PROCEDURE get_max_salary(OUT maxsalary INT)

BEGIN
  SELECT MAX(Salary) INTO maxsalary FROM Employee;  
END &&  
DELIMITER ;  

After successful execution, the procedure can be called as mentioned below. The OUT parameter tells the database system that its value goes out from the procedure. This value is passed to to a session variable @S in the CALL statement as shown below:

CALL get_max_salary(@S);
SELECT @S;

This will produce the result as shown below:

+------+
| @S   |
+------+
| 3100 |
+------+
1 row in set (0.00 sec)

Create Procedure with INOUT Parameter

In this procedure, the INOUT parameter is used with parameter named 'var1' of integer type. Its body part fetches the Salary from the table using specified EmpID and stores it into the same variable var1. The var1 first acts as the IN parameter and then OUT parameter.

DELIMITER &&  
CREATE PROCEDURE get_salary(INOUT var1 INT)

BEGIN
  SELECT Salary INTO var1 FROM Employee WHERE EmpID = var1;  
END &&  
DELIMITER ;  

After successful execution, the procedure can be called as follows:

SET @S = '5';  
CALL get_salary(@S);  
SELECT @S;  

This will produce the result as shown below:

+------+
| @S   |
+------+
| 3000 |
+------+
1 row in set (0.00 sec)

Drop Procedure

Once a procedure is created in MariaDB, it can be removed by using DROP PROCEDURE statement. The DROP PROCEDURE statement requires the ALTER ROUTINE privilege for it. By default, MariaDB automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator.

Syntax

The syntax of removing a procedure in MariaDB is given below:

DROP PROCEDURE [IF EXISTS] procedure_name; 

The IF EXISTS is an optional parameter that conditionally removes procedure only if it exists on the database. If a procedure is deleted which does not exist, it will raise an error.

Example:

To drop a procedure named CalcMaxSalary, the following statement can be used:

DROP PROCEDURE CalcMaxSalary;