Procedure in MariaDB

MariaDB Procedure
A procedure in MariaDB can be simply understood as a stored program that is used to pass parameters into it. However, unlike functions, it does not return a value.

MariaDB Create Procedure:

Syntax: To create a procedure in MariaDB database.

CREATE
[ DEFINER = { CURRENT_USER | user_name } ]
PROCEDURE procedure_name
[ (parameter data type, parameter data type) ]
[ 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;


Parameters:

• DEFINER clause: It is an optional clause which is used to specify a definer, by including the DEFINER clause where user_name is the definer for the procedure. By default, the definer is the user that created the procedure.
• procedure_name: It is used to specify the name of the procedure.
• Parameters: It is used to specify the parameters passed to the procedure. It can be of three types:
1. IN:
It is used to specify that the parameter can be referenced by the procedure, however its value can not be overwritten by the procedure.

2. OUT:
It is used to specify that the parameter can not be referenced by the procedure, however its value can be overwritten by the procedure.
3. IN OUT:
It is used to specify that the parameter can be referenced by the procedure, however its value can be overwritten by the procedure.
• LANGUAGE SQL: It is used to specify the Language SQL in the syntax for portability. It has no impact on the procedure.
• DETERMINISTIC: It is used to specify that the procedure will always return one result given a set of input parameters.
• NOT DETERMINISTIC: It is used to specify that the procedure may return a different result given a set of input parameters.
• CONTAINS SQL: It is used to specify an informative clause to tell that the procedure contains SQL, but the database does not verify that this is true.
• NO SQL: It is used to specify an informative clause that is not used and will have no impact on the procedure.
• READS SQL DATA: It is used to specify an informative clause to tell that the procedure will read data using SELECT statements but does not modify any data.
• MODIFIES SQL DATA: It is used to specify an informative clause to tell that the procedure will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.
• declaration_section: It is used to specify the place in the procedure to declare local variables.
• executable_section: It is used to specify the place in the procedure to enter the code for the procedure.

Example:

DELIMITER // CREATE procedure Calculate ( OUT final_value INT ) DETERMINISTIC BEGIN DECLARE total_value INT; SET total_sum = 10; label1: WHILE total_sum &lt;= 100 DO SET total_sum = total_sum * 10; END WHILE label1; SET final_value = total_sum; END; // DELIMITER ;

Explanation:
Here we are creating a procedure Calculate in MariaDB database.

MariaDB DROP Procedure:

In MariaDB, we can also drop an already existing procedure from the database.

Syntax: To drop a procedure from MariaDB database.

DROP PROCEDURE [ IF EXISTS ] proc_name;


Parameters:
proc_name: It is used to specify the name of the procedure to drop.

Example:

DROP PROCEDURE Calculate;
Please Share