MariaDB: Procedures

This MariaDB tutorial explains how to create and drop procedures in MariaDB with syntax and examples.

What is a procedure in MariaDB?

In MariaDB, a procedure is a stored program that you can pass parameters into. It does not return a value like a function does.

Create Procedure

Just as you can create procedures in other languages, you can create your own procedures in MariaDB. Let's take a closer look.

Syntax

The syntax to create a procedure in MariaDB is:

CREATE 
[ DEFINER = { CURRENT_USER | user_name } ] 
PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]

[ LANGUAGE SQL
| DETERMINISTIC
| NOT DETERMINISTIC
| { CONTAINS SQL 
  | NO SQL
  | READS SQL DATA
  | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'comment_value'

BEGIN

   declaration_section

   executable_section

END;

DEFINER clause

Optional. If not specified, the definer is the user that created the procedure. If you wish to specify a different definer, you must include the DEFINER clause where user_name is the definer for the procedure.

procedure_name

The name to assign to this procedure in MariaDB.

parameter

One or more parameters passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:

1. IN - The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
2. OUT - The parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
3. IN OUT - The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

LANGUAGE SQL

It is in the syntax for portability but will have no impact on the function.

DETERMINISTIC

It means that the function will always return one result given a set of input parameters.

NOT DETERMINISTIC

It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.

CONTAINS SQL

It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.

NO SQL

It is an informative clause that is not used and will have no impact on the function.

READS SQL DATA

It is an informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.

MODIFIES SQL DATA

It is an informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.

declaration_section

The place in the procedure where you declare local variables.

executable_section

The place in the procedure where you enter the code for the procedure.

Example

Let's look at an example that shows how to create a procedure in MariaDB:

DELIMITER //

CREATE procedure CalcValue ( OUT ending_value INT )
DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 50;

   label1: WHILE total_value = 3000 DO
     SET total_value = total_value * 2;
   END WHILE label1;

   SET ending_value = total_value;

END; //

DELIMITER ;

You could then reference your new procedure as follows:

CALL CalcValue (@variable_name);

SELECT @variable_name;

Drop procedure

Once you have created your procedure in MariaDB, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in MariaDB is:

DROP procedure [ IF EXISTS ] <strong>procedure_name</strong>;

procedure_name

The name of the procedure that you wish to drop.

Example

Let's look at an example of how to drop a procedure in MariaDB.

For example:

DROP procedure CalcValue;

This example would drop the procedure called CalcValue.