RETURN Statement
The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below the subprogram call.
The subprogram can contain few RETURN statements, none of that need be the last lexical statement. The subprogram is completed if any of them is executing instantly. Though, to have multiple exit points in a subprogram is a poor programming practice.
In procedures, the RETURN statement cannot contain an expression. The statement merely returns a control to the caller before the general end of the procedure is reached. Though, in functions, a RETURN statement should contain an expression, that is evaluated when the RETURN statement is executed. The resultant value is assigned to the function identifier that acts like a variable of the type specified in the RETURN clause. Now observe how the function balance returns the balance of a specified bank account:
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END balance;
The illustration below shows that the expression in a function RETURN statement can be randomly complex:
FUNCTION compound (
years NUMBER,
amount NUMBER,
rate NUMBER) RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
The function should contain at least one RETURN statement. Or else, the PL/SQL raises the predefined exception PROGRAM_ERROR at the run time.