MySQL Stored Procedures

Yusuf SEZER

5 years ago

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.

CREATE PROCEDURE MyStoredProcedure(parameter1, parameter2, parameterN)
BEGIN
 -- MySQL ve SQL commands
END;

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.

DELIMITER //
CREATE PROCEDURE ListProducts_SP_Example()
BEGIN
  SELECT * FROM products;
END//
DELIMITER ;

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.

CALL MyStoredProcedures(parameter1, parameter2, parameterN);

1	CALL MyStoredProcedures(parameter1, parameter2, parameterN);

Let’s run the stored procedure named MyStoredProcedure().

CALL 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.

DECLARE variable_name datatype(size) DEFAULT default_value;

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.

DECLARE MyName VARCHAR(50) DEFAULT '';
SET MyNAme = 'Yusuf Sefa SEZER';

1 2	DECLARE MyName VARCHAR(50) DEFAULT ''; SET MyNAme = 'Yusuf Sefa SEZER';

Lets use it in a stored procedure.

DELIMITER //
CREATE PROCEDURE SakliYordam()
BEGIN
  DECLARE MyName VARCHAR(50) DEFAULT ''; 
  SET MyNAme = 'Yusuf Sefa SEZER';
END//
DELIMITER ;

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.

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 ;

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;

DELIMITER //
CREATE PROCEDURE Legnht_of_Name(myanme VARCHAR(50))
BEGIN
  SELECT CHAR_LENGTH(myname) AS Name_Length;
END//
DELIMITER ;

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.

CALL Legnht_of_Name('Yusuf SEZER');

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

DELIMITER //
CREATE PROCEDURE Legnht_of_Name(IN mynameVARCHAR(50), OUT namelength INT)
BEGIN
  SET namelength = CHAR_LENGTH(myname);
END//
DELIMITER ;

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;

SET @namelength = 0;
CALL Legnht_of_Name('Yusuf SEZER', @namelength);
SELECT @namelength;

SET @namelength = 0;

CALL Legnht_of_Name('Yusuf SEZER', @namelength);

SELECT @namelength;

INOUT Usage;

INOUT is a combination of IN and OUT.

DELIMITER //
CREATE PROCEDURE Legnht_of_Name_Plus_Counter(IN myname VARCHAR(50), INOUT counter INT)
BEGIN
  SET counter= counter + CHAR_LENGTH(myname);
END//
DELIMITER ;

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;

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;

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

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 ;

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;

CALL AddProduct('MySQL Book', 49, 59);

1	CALL AddProduct('MySQL Book', 49, 59);

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