In this article, I will talk a bit about Stored Procedures and demonstrate a small example of their use in MySQL/MariaDB.
Environment used:
Computer: Gateway Notebook
Operating system: OpenSuse 11.2
Database: MariaDB 5.1.42
What is a Stored Procedure?
Stored Procedures are simply a set of SQL statements that are executed inside the database.
It’s like writing a program inside the database itself to run everything there.
General syntax:
CREATE PROCEDURE (type_param param_1 data_type, …)
[BEGIN]
routine_body;
[END]
When passing parameters to a procedure, you can define their type:
- IN => input parameter.
- OUT => output parameter.
- INOUT => input and output parameter.
If no type is specified for the parameter, the default is IN.
Practical example:
We will create a procedure that will “populate” a table called Cliente with the name and email.
1. In the terminal, connect to the MariaDB.
mysql -u root -p
2. Database creation::
CREATE DATABASE mysqllabs;
3. Creation of the CLIENTE table:
CREATE TABLE IF NOT EXISTS mysqllabs.cliente (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR(45) NOT NULL ,
email VARCHAR(80) NOT NULL ,
PRIMARY KEY (id) )
ENGINE = Maria;
The ID field is auto-increment and has two other attributes: name and email.
The table was created using the Maria storage engine. If you’re not familiar with Storage Engines, take a look at this article written by Alexandre Almeida.
(http://www.mysqllabs.com/joomla/index.php?option=com_content&view=article&id=55:storageengine1&catid=1:latest-news&Itemid=100).
4. We will create a procedure called INS_CLIENTE that will have NAME and EMAIL as input parameters. The data will be inserted into the CLIENTE table.
delimiter //
CREATE PROCEDURE mysqllabs.ins_cliente(in pnome VARCHAR(45)
,in pemail VARCHAR(80) )
BEGIN
INSERT INTO cliente(name, email)
VALUES(pname, pemail);
END
//
Now you might ask me: What does the DELIMITER mean before creating the procedure?
Changing the line delimiter prevents MariaDB from interpreting the ‘;’ inside the commands listed within a trigger, procedure, etc. If we left the ‘;’ (semicolon) at the end of the INSERT, our command would end prematurely and incorrectly.
5. To execute the procedure, simply use the CALL command.
DELIMITER ;
call mysqllabs.ins_cliente(‘julio’, ‘julio@mysqllabs.com’);
6. To check if the insertion was made in the table, run the following command:
select * from mysqllabs.cliente;
After creating a stored procedure in MySQL, how do I query it?
After being created and compiled, stored procedures are inserted into a table called ROUTINES in the INFORMATION_SCHEMA database, which is MySQL’s data dictionary.
To list them, simply run the following command:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
If you prefer, you can use the command: SHOW PROCEDURE STATUS;
It returns characteristics of the routine, such as name, type, creator, modification date, and creation date.
Another option is to use the command: SHOW PROCEDURE STATUS LIKE ‘%ins%’;
In the LIKE clause, you provide the name of the procedure or a part of its description to search for; in our case, I used the string “INS”.
I hope you enjoyed it, and see you next week!
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
