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:
- IN - The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
- OUT - The parameter can not 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
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 ] procedure_name;
- 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.
Advertisements