---

RETURN Statement

The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the return value. For more information, see "Using the RETURN Statement".

Syntax

Description of the illustration return_statement.gif

Keyword and Parameter Description

expression

A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the RETURN statement is executed, the value of expression is assigned to the function identifier.

Usage Notes

Do not confuse the RETURN statement with the RETURN clause in a function spec, which specifies the datatype of the return value.

A subprogram can contain several RETURN statements. Executing any of them completes the subprogram immediately. The RETURN statement might not be positioned as the last statement in the subprogram.

In procedures, a RETURN statement cannot contain an expression. The statement just returns control to the caller before the normal end of the procedure is reached.

In functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier. In functions, there must be at least one execution path that leads to a RETURN statement. Otherwise, PL/SQL raises an exception at run time.

The RETURN statement can be used in an anonymous block to exit the block (and all enclosing blocks), but the RETURN statement cannot contain an expression.

Example

The following example demonstrates the RETURN statement using a variable, an expression, or no argument at all:

DECLARE
   FUNCTION num_rows (table_name VARCHAR2) RETURN user_tables.num_rows%TYPE
   IS
        howmany user_tables.num_rows%TYPE;
   BEGIN
      EXECUTE IMMEDIATE 'SELECT num_rows FROM user_tables ' ||
         'WHERE table_name = ''' || UPPER(table_name) || ''''
         INTO howmany;
-- A function can compute a value, then return that value.
      RETURN howmany;
   END num_rows;

   FUNCTION double_it(n NUMBER) RETURN NUMBER
   IS
   BEGIN
-- A function can also return an expression.
      RETURN n * 2;
   END double_it;

   PROCEDURE print_something
   IS
   BEGIN
      dbms_output.put_line('Message 1.');
-- A procedure can end early by issuing RETURN with no value.
      RETURN;
      dbms_output.put_line('Message 2 (never printed).');
   END;
BEGIN
   dbms_output.put_line('EMPLOYEES has ' || num_rows('employees') || ' rows.');
   dbms_output.put_line('Twice 100 is ' || double_it(n => 100) || '.');
   print_something;
END;
/

Related Topics

Functions, Procedures