Blog

This MySQL 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_nameThe name to assign to this function in MySQL.parameterOne 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_datatypeThe data type of the function’s return value.declaration_sectionThe place in the function where you declare local variables.executable_sectionThe 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 ] function_name;

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

Next Topic : Click Here