MySQL Stored Procedures

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.

Let’s list all products in the products table using the stored procedure.

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.

Let’s run the stored procedure named 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.

The SET keyword is used to assign a value to the variable.

Sample; Let’s create a variable named MyName and set a value.

Lets use it in a stored procedure.

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.

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;

The IN parameter is used when the parameter type is not written.

We can run the above stored procedure as follows.

How to use IN, OUT and INOUT Parameters in Mysql Stored Procedure

In and OUT Usage;

In is the default usage. W

We can use above stored procedure as follows;

INOUT Usage;

INOUT is a combination of IN and OUT.

We can use above stored procedure as follows;

Insert Stored Procedure in MySQL

We can use above stored procedure as follows;

Stored procedures are run similarly in programming languages like PHP, Java, C#.