MariaDB: RETURN Statement

This MariaDB tutorial explains how to use the RETURN statement in MariaDB with syntax and examples.

Description

In MariaDB, the RETURN statement is used when you are want to exit a function and return the result of the function. It can also be used to terminate a LOOP and then exit with the result.

Syntax

The syntax for the RETURN statement in MariaDB is:

RETURN result;

Parameters or Arguments

result

The result that is to be returned by the function.

Note

• The RETURN statement can be used in a function to create an exit point. Each function you create in MariaDB must have at least one RETURN statement, though it can have more than one RETURN statement if there are multiple exit points in the function.
• The RETURN statement can also be used to terminate a LOOP and then exit with the function result.

Example

Let's look at an example that shows how to use the RETURN statement in MariaDB:

DELIMITER //

CREATE FUNCTION CalcValue ( starting_value INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 0;

   label1: LOOP
     SET total_value = total_value + starting_value;
     IF total_value  700 THEN
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   RETURN total_value;

END; //

DELIMITER ;

In this RETURN example, the function called CalcValue will exit when it encounters the RETURN statement and return the value stored in the total_value variable as the result of the function.

You could have also used the RETURN statement to terminate the loop called label1. For example:

DELIMITER //

CREATE FUNCTION CalcValue ( starting_value INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 0;

   label1: LOOP
     SET total_value = total_value + starting_value;
     IF total_value  3000 THEN
       ITERATE label1;
     END IF;
     RETURN total_value;
   END LOOP label1;

   RETURN starting_value;

END; //

DELIMITER ;

In this RETURN example, we use the RETURN statement twice.

The first RETURN statement is within the LOOP statement and when encountered will exit the LOOP, exit the function, and return the value stored in the variable called total_value.

The second RETURN statement is used at the end of the function to exit the function and return the value stored in the starting_value variable.

In this example, we have used the RETURN statement to create 2 exit points in the function.