Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The article provides information about MySQL stored procedures. Creating a stored procedure and stored procedure types are explained with examples.
What is MySQL Stored Procedures?
Stored procedures are compiled SQL commands stored in DBMS.
Codes written in stored procedures run faster than SQL commands because they are compiled only when they run first.
Advanced VTY systems such as “SQL Server, Oracle, Sybase” support Stored Procedures.
MySQL stored procedures are available after MySQL 5.0.
Stored procedures are often used to store repetitive queries.
Create a Stored Procedure in MySQL
Stored procedures are created with the keyword CREATE PROCEDURE.
1 2 3 4 | CREATE PROCEDURE MyStoredProcedure(parameter1, parameter2, parameterN) BEGIN -- MySQL ve SQL commands END; |
Let’s list all products in the products table using the stored procedure.
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE ListProducts_SP_Example() BEGIN SELECT * FROM products; END// DELIMITER ; |
What is delimiter in mysql stored procedure?
We can replace the default MySQL separator (;) with the DELIMITER keyword before the stored procedure. In our example, we set the separator as (//).
Changing the separator to (//) ensures that the SQL and MySQL commands to be written are stored as a single command.
CALL a Stored Procedure in MySQL
The most important nice feature of the stored procedure is that it can be used over and over again after created.
The CALL keyword is used to run the stored procedure.
1 | CALL MyStoredProcedures(parameter1, parameter2, parameterN); |
Let’s run the stored procedure named MyStoredProcedure().
1 | CALL MyStoredProcedure(); |
When the stored procedure is executed, SQL commands in the stored procedure will be executed.
Using Variables in MySQL Stored Procedures
For the definition of variables in MySQL, you can look the article”How do I declare a variable in MySQL?“.
However, the DECLARE keyword is used to create variables within the stored procedure.
1 | DECLARE variable_name datatype(size) DEFAULT default_value; |
The SET keyword is used to assign a value to the variable.
Sample; Let’s create a variable named MyName and set a value.
1 2 | DECLARE MyName VARCHAR(50) DEFAULT ''; SET MyNAme = 'Yusuf Sefa SEZER'; |
Lets use it in a stored procedure.
1 2 3 4 5 6 7 | DELIMITER // CREATE PROCEDURE SakliYordam() BEGIN DECLARE MyName VARCHAR(50) DEFAULT ''; SET MyNAme = 'Yusuf Sefa SEZER'; END// DELIMITER ; |
When we run the stored procedure, it will not give any result since the variable is not used.
To use the variable in SQL commands, it will be enough to Select the variable name.
1 2 3 4 5 6 7 8 | DELIMITER // CREATE PROCEDURE MyStoredProcedure() BEGIN DECLARE MyName VARCHAR(50) DEFAULT ''; SET MyName = 'Yusuf Sefa SEZER'; SELECT CHAR_LENGTH(MyName) AS Lenght_of_MyName; END// DELIMITER ; |
When the stored procedure is executed, it will return the length of the value of the MyName variable.
Variables can only be used between BEGIN and END, where they are defined.
Variables are inaccessible except for BEGIN and END, where they are defined.
Stored Procedure Parameters in MySQL
By passing parameters to stored procedures, various operations can be performed according to the parameter.
Stored procedure parameters are written in stored procedure brackets.
Stored procedure parameters are divided into three as IN, OUT and INOUT.
A simple parameter usage example;
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE Legnht_of_Name(myanme VARCHAR(50)) BEGIN SELECT CHAR_LENGTH(myname) AS Name_Length; END// DELIMITER ; |
The IN parameter is used when the parameter type is not written.
We can run the above stored procedure as follows.
1 | CALL Legnht_of_Name('Yusuf SEZER'); |
How to use IN, OUT and INOUT Parameters in Mysql Stored Procedure
In and OUT Usage;
In is the default usage. W
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE Legnht_of_Name(IN mynameVARCHAR(50), OUT namelength INT) BEGIN SET namelength = CHAR_LENGTH(myname); END// DELIMITER ; |
We can use above stored procedure as follows;
1 2 3 | SET @namelength = 0; CALL Legnht_of_Name('Yusuf SEZER', @namelength); SELECT @namelength; |
INOUT Usage;
INOUT is a combination of IN and OUT.
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE Legnht_of_Name_Plus_Counter(IN myname VARCHAR(50), INOUT counter INT) BEGIN SET counter= counter + CHAR_LENGTH(myname); END// DELIMITER ; |
We can use above stored procedure as follows;
1 2 3 4 5 6 7 8 9 10 11 | SET @namelength= 0; CALL Legnht_of_Name_Plus_Counter('Yusuf SEZER', @namelength); SELECT @namelength; SET @namelength= 1; CALL Legnht_of_Name_Plus_Counter('Yusuf SEZER', @namelength); SELECT @namelength; SET @namelength= 2; CALL Legnht_of_Name_Plus_Counter('Yusuf SEZER', @namelength); SELECT @namelength; |
Insert Stored Procedure in MySQL
1 2 3 4 5 6 | DELIMITER // CREATE PROCEDURE AddProduct(IN product_name VARCHAR(50), IN product_price DECIMAL, IN product_id INT) BEGIN INSERT INTO Products(productname, productprice, id) VALUES(product_name, product_price, product_id); END// DELIMITER ; |
We can use above stored procedure as follows;
1 | CALL AddProduct('MySQL Book', 49, 59); |
Stored procedures are run similarly in programming languages like PHP, Java, C#.
