Blog

In this guide, we will explain how to create and drop procedures in MySQL with syntax and examples.

What is a procedure in MySQL?

In MySQL, a procedure is a stored program that you can pass parameters into. It does not return a value as a function does.

Create Procedure

Just as you can create procedures in other languages, you can create your own procedures in MySQL. Let’s take a closer look.

Syntax

The syntax to create a procedure in MySQL is:

CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]

BEGIN

   declaration_section

   executable_section

END;

procedure_nameThe name to assign to this procedure in MySQL.parameter

Optional. One or more parameters are passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:

1. IN – The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
2. OUT – The parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
3. IN OUT – The parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.

declaration_sectionThe place in the procedure where you declare local variables. executable_sectionThe place in the procedure where you enter the code for the procedure.

Example

Let’s look at an example that shows how to create a procedure in MySQL:

DELIMITER //

CREATE procedure CalcIncome ( OUT ending_value INT )

BEGIN

   DECLARE income INT;

   SET income = 50;

   label1: WHILE income <= 3000 DO
     SET income = income * 2;
   END WHILE label1;

   SET ending_value = income;

END; //

DELIMITER ;

You could then reference your new procedure as follows:

CALL CalcIncome (@variable_name);

SELECT @variable_name;

Drop procedure

Once you have created your procedure in MySQL, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in MySQL is:

DROP procedure [ IF EXISTS ] procedure_name;

procedure_nameThe name of the procedure that you wish to drop.

Example

Let’s look at an example of how to drop a procedure in MySQL.

For example:

DROP procedure CalcIncome;

This example would drop the procedure called CalcIncome.

Next Topic : Click Here