Term: RETURN

Definition:
In Oracle PL/SQL, the term RETURN can be used either as a clause or a statement. It shows different behavior in each context.

1. As a Clause

As a clause, RETURN is a mandatory element in a function definition. It defines the data type of the value returned by the function. It can be a primitive data type like NUMBER, VARCHAR2, DATE or UDT (database collection types). A function can logically return only one value from the body. (To work around this limitation, multiple values are often concatenated and returned as a single value, which are then split back apart for further use.)

Example Syntax:

CREATE OR REPLACE FUNCTION [FUNCTION NAME]

RETURN [RETURN TYPE]

IS

BEGIN

...

END;

Example Usage:

The function "F_RET_VAL" is created which returns a NUMBER value, i.e. 1 from the function body.

CREATE OR REPLACE FUNCTION F_RET_VAL 

RETURN NUMBER

IS

BEGIN

RETURN 1;

END;

2. As a PL/SQL Statement

In PL/SQL code the RETURN keyword can appear in any subprogram as a statement. As soon as it is encountered in the subprogram body, Oracle server immediately sends the execution control back to the immediate calling code or host environment. No further statements are processed in the block following the RETURN statement.

Note that a function can contain RETURN both as a clause and a statement.

Example Syntax:

CREATE OR REPLACE [SUBPROGRAM] [NAME]

IS

BEGIN

statement 1;

...

RETURN;

statement 2;

END;

In the above syntax, when the execution pointer comes to the RETURN statement, Oracle skips all subsequent statements and returns to the calling environment.

Example Usage:

In the example below, a procedure named "P_RET_VAL" is created which returns the control to the calling environment based on the value of P_EMPNO. It returns if it is NULL.

CREATE OR REPLACE PROCEDURE P_RET_VAL (P_EMPNO NUMBER DEFAULT NULL)

IS

BEGIN

IF P_EMPNO IS NULL THEN 

 RETURN;

ELSE

 DBMS_OUTPUT.PUT_LINE(P_EMPNO);

END IF;

END;