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:
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 ;