MySQL: Procedures

This MySQL tutorial explains 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 like 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_name

The name to assign to this procedure in MySQL.

parameter

Optional. One or more parameters 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_section

The place in the procedure where you declare local variables.

executable_section

The 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_name

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