MySQL: Functions

This tutorial explains how to create and drop functions in MySQL with syntax and examples.

What is a function in MySQL?

In MySQL, a function is a stored program that you can pass parameters into and then return a value.

Create Function

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

Syntax

The syntax to create a function in MySQL is:

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype

BEGIN

   declaration_section

   executable_section

END;

function_name

The name to assign to this function in MySQL.

parameter

One or more parameters passed into the function. When creating a function, all parameters are considered to be IN parameters (not OUT or INOUT parameters) where the parameters can be referenced by the function but can not be overwritten by the function.

return_datatype

The data type of the function's return value.

declaration_section

The place in the function where you declare local variables.

executable_section

The place in the function where you enter the code for the function.

Example

Let's look at an example that shows how to create a function in MySQL:

DELIMITER //

CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE income INT;

   SET income = 0;

   label1: WHILE income = 3000 DO
     SET income = income + starting_value;
   END WHILE label1;

   RETURN income;

END; //

DELIMITER ;

You could then reference your new function as follows:

SELECT CalcIncome (1000);

Drop Function

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

Syntax

The syntax to a drop a function in MySQL is:

DROP FUNCTION [ IF EXISTS ] <strong>function_name</strong>;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in MySQL.

For example:

DROP FUNCTION CalcIncome;

This example would drop the function called CalcIncome.