MySQL - RETURN Statement

MySQL RETURN Statement

The RETURN statement in MySQL is used to end the stored functions. Each stored function should have at least one RETURN statement. This is used only in functions in stored procedures triggers or, events you can use LEAVE instead of RETURN.

Syntax

Following is the syntax of the RETURN statement is MySQL −

RETURN expression

Where, expression is the value to be returned.

Example 1

Following query demonstrates how to use the RETURN statement with in a function.

DELIMITER //
CREATE FUNCTION Sample (bonus INT)
   RETURNS INT
   BEGIN
      DECLARE income INT;
      SET income = 0;
      myLabel: LOOP
         SET income = income + bonus;
         IF income < 10000 THEN
            ITERATE myLabel;
         END IF;
         LEAVE myLabel;
      END LOOP myLabel;
   RETURN income;
END; //
DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

Output

The above mysql query will produce the following output −

Example 2

Suppose we have created a table named Emp in the database using the CREATE statement as shown below −

CREATE TABLE Emp(
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

And we have inserted three records in the Emp table as −

INSERT INTO Emp VALUES 
('Amit', DATE('1970-01-08'), 'Hyderabad'),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam'),
('Sudha', DATE('1980-11-06'), 'Vijayawada');

Following query creates a function named getDob()which accepts the name of the employee, retrieves and returns the value of DOB column.

DELIMITER //
CREATE FUNCTION test.getDob(emp_name VARCHAR(50))
   RETURNS DATE
   DETERMINISTIC
   BEGIN
      declare dateOfBirth DATE;
      select DOB into dateOfBirth from test.emp where Name = emp_name;
      return dateOfBirth;
   END//
DELIMITER ;

If you call the function you can get date of birth of an employee as shown below −

SELECT getDob('Amit');

Output

Following is the output of the above query −