MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB - Stored Procedures



A procedure (often called a stored procedure) is a collection of pre-compiled SQL statements stored inside the database. It is a subroutine or a subprogram in the regular computing language. A procedure always contains a name, parameter lists, and SQL statements. We can invoke the procedures by using triggers, other procedures and applications such as Java, Python, PHP, etc. It was first introduced in MariaDB version 5. Presently, it can be supported by almost all relational database systems.

If we consider the enterprise application, we always need to perform specific tasks such as database cleanup, processing payroll, and many more on the database regularly. Such tasks involve multiple SQL statements for executing each task. This process might easy if we group these tasks into a single task. We can fulfill this requirement in MariaDB by creating a stored procedure in our database.

A procedure is called a recursive stored procedure when it calls itself. Most database systems support recursive stored procedures. But, it is not supported well in MariaDB.

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.
  • Stored procedures are reusable and transparent to any applications.
  • A procedure is always secure. The database administrator can grant permissions to applications that access stored procedures in the database without giving any permissions on the database tables.

Syntax

The syntax of using Procedures statement is given below:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]

BEGIN

   declaration_section

   executable_section

END;

Parameters

procedure_name Required. Specify the name to assign to this procedure.

Create Procedure without Parameter

Create Procedure with IN Parameter

Create Procedure with OUT Parameter

Create Procedure with INOUT Parameter

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.

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; 

5