Procedures are sort of functions in a database. Procedures are created when you want to perform a task repetitively.
MariaDB procedure is a stored program that is used to pass parameters into it. It does not return a value as a function does.
You can create and drop procedures like functions.
# Create Procedure (MariaDB):
You can create your procedure just like you create a function in MariaDB.
Syntax:
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;
Parameter Explanation
DEFINER clause: Optional.
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:
IN: The parameter can be referenced by the procedure. The value of the parameter cannot be overwritten by the procedure.
OUT: The parameter cannot be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
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
Create a procedure named "CalcValue" in MariaDB database.
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 ;
Procedure is created successfully:
MariaDB interview questions
You can refer your new procedure as follows
MariaDB Procedure 3
MariaDB DROP Procedure:
You can drop procedure by using the following command:
Syntax:
DROP procedure [ IF EXISTS ] procedure_name;
Parameter Explanation:
procedure_name: It specifies the name of the procedure that you want to drop.
Example
DROP procedure CalcValue;
You can see that the procedure is dropped now and it is not available in the list.
