MariaDB create procedure – Complete Tutorial

In this MariaDB tutorial, we will learn, how to create a procedure in MariaDB. Here we saw a few examples of the “MariaDB create procedure” and with the following topics.

• MariaDB create procedure
• MariaDB create procedure syntax error
• How to create procedure with parameters in MariaDB
• MariaDB create procedure not parameters
• How to create procedure command line in MariaDB
• MariaDB create procedure table
• MariaDB show create procedure
• MariaDB grant create procedure

Create procedure in MariaDB

A stored program is a procedure in MariaDB that helps us in passing the parameter into it.

The full syntax to create the procedure is given below.

CREATE   
[ DEFINER = { current_user | user_name } ]   
PROCEDURE procedure_name   
[ (parameter_name data type, parameter_name 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  
   declare_section  
   execution_section  
END;   

Where parameters are,

DEFINER clause
This one is Optional. The definer clause is used to define the user who creates the function. So to use the different definer, we need to write the DEFINER keyword with user_name, and this user_name is the new definer of the function.
Procedure_name
The name that we want to give to a new Procedure in MariaDB.
Parameter_name
More than one parameter can be provided to the function. While creating a new procedure, all parameter needs to be the input parameters.
Return_datatype
We mention what kind of value the procedure will return, after doing some calculation or processing, if the procedure returns the integer, then the data type of the procedure will be INT type.
LANGUAGE SQL
When LANGUAGE SQL is specified, then it determines that the procedure returns only a single result provided a list of parameters to the procedure.
NOT DETERMINISTIC
It is just the opposite of the clause LANGUAGE SQL, It may give different results provided the list of parameters to the procedure.
CONTAINS SQL
CONTAINS SQL clause is default clause and an instructive clause that instructs MariaDB that the procedure is going to have SQL.
READS SQL DATA
It is an instructive clause that instructs the MariaDB procedure that reads data with help of SELECT statements but it will not change the data.
MODIFIES SQL DATA
It is an instructive clause that instructs the MariaDB procedure to change SQL data using data definition language statements like INSERT, DELETE, UPDATE.
Declare_section
In the declare section, local variables are declared.
Execution_section
In the execution_section, where SQL query or code is written.

Let’s create the procedure as calci using the below code

DELIMITER //

CREATE procedure Calci ( OUT ending_value INT )
DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 10;

   label1: WHILE total_value <= 300 DO
     SET total_value = total_value * 4;
   END WHILE label1;

   SET ending_value = total_value;

END; //

DELIMITER ;

Call this procedure.

CALL Calci(@hello);
SELECT @hello;

In the above code, we are storing the result from the Calci into @hello. Then select or view the information from @hello in the SELECT statement.

The output shows the result as 640.

This is how to create a procedure in MariaDB.

Also, check: How to Change Column in MariaDB

MariaDB create procedure syntax error

Suppose we are a user in a database company or we are the database developer of any company which exists in the United States. While executing some of the procedures based queries on the database, we get the error related to syntax.

To resolve the error problems we should always visit the official documentation of MariaDB. This kind of documentation contains all the rules to write the query correctly.

MariaDB create procedure with parameters

The procedure in the MariaDB can contain the parameters like IN for input and OUT for output, as we have learned in the above first sub-section of this tutorial. Parameters are used to exchange or pass the data between stored procedures and functions.

Let’s create the procedure as ‘simplecount‘ that will count the number of records in the table large_csv.

DELIMITER //

CREATE PROCEDURE simplecount (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM large_csv;
 END;
//

DELIMITER ;

CALL simplecount(@a);

SELECT @a;

From the above output, we can see that large_csv table contains 28,338 records.

This is how to create a procedure with parameters in MariaDB.

Also, read: MariaDB Update Statement

MariaDB create procedure not parameters

In the above sub-section of this section, we have created the procedure with parameters, so here we will create the procedure with no parameters in the MariaDB.

Here we are going to create the procedure as no_paramater that will show all data of the table large_csv.

DELIMITER //

CREATE PROCEDURE no_parameter()
 BEGIN
  SELECT * FROM large_csv;
 END;
//

DELIMITER ;

CALL no_parameter();

This is how to create a procedure without parameters in MariaDB.

Read: How to Remove User in MariaDB

MariaDB create procedure command line

In the command line, we can create the MariaDB procedure, the command line is like a Linux terminal which is also called command-line interface ( CLI ), so here we are going to create the procedure in the command line.

Let’s create the same procedure Calci using the command line.

DELIMITER //

CREATE procedure Calci ( OUT ending_value INT )
DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 10;

   label1: WHILE total_value <= 300 DO
     SET total_value = total_value * 4;
   END WHILE label1;

   SET ending_value = total_value;

END; //

DELIMITER ;

As we can see that we have created a procedure in the command line.

Read: How to Grant All Privileges in MariaDB

MariaDB create procedure table

The procedure can create the new table or we can create the procedure that will create a new table in the MariaDB using the CREATE PROCEDURE statement.

Let’s create the procedure for creating the new table.

DELIMITER //

CREATE procedure new_tbl ()
DETERMINISTIC

BEGIN

   CREATE TABLE tbl(id INT, NAME VARCHAR(10));

END; //

DELIMITER ;

The above code in the block BEGIN and END will create the new table as tbl with two columns id and NAME.

Call this procedure.

CALL new_tbl;
SHOW COLUMNS FROM tbl;

The output shows the created table from procedure new_tbl.

This is how to create a table from a procedure in MariaDB.

Read: How to Create View in MariaDB

MariaDB show create procedure

Here, we will create the procedure that will show the table in the current MariaDB, the same command will be used here and that is the CREATE PROCEDURE.

Let’s create the procedure as show_tbl to know the number of tables in the current database.

DELIMITER //

CREATE procedure show_tbl ()
DETERMINISTIC

BEGIN

   SHOW TABLES;

END; //

DELIMITER ;

Call the procedure using the below query.

CALL show_tbl;

So here in the output, we showed all tables in the current database using the procedure.

Read: Replace Function in MariaDB

MariaDB grant create procedure

In MariaDB, we can grant the privileges on various MariaDB objects, we can grant users the capability to EXECUTE these procedures in MariaDB.

The syntax is given below.

GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;

• EXECUTE: which means the capability to execute a procedure.
• PROCEDURE: This is used when the privilege to be granted on a procedure.
• object: It is the database name to whom privileges is granted. If we are granting the privileges to a procedure then it should the name of that procedure.
• user: The name of the user who is going to have the EXECUTE privileges.

Let’s grant the EXECUTE privilege to user root on procedure Calci using the below code.

GRANT EXECUTE ON PROCEDURE Calci TO 'root'@'localhost';

This is how to grant permission to create a procedure in MariaDB.

You may also like to read the following tutorials on MariaDB.

• MariaDB Vs SQLite
• MariaDB Timestamp
• MariaDB DateTime Tutorial 
• MariaDB ERROR 1064
• How to import CSV files in MariaDB
• MariaDB Reset Root Password
• How to Create Trigger in MariaDB

Here we learned how to create a procedure in MariaDB and a few examples of MariaDB create procedure.

• MariaDB create procedure
• MariaDB create procedure syntax error
• MariaDB create procedure with parameters
• MariaDB create procedure not parameters
• MariaDB create procedure command line
• MariaDB create procedure table
• MariaDB show create procedure
• MariaDB grant create procedure