RETURN Statement in PL SQL
The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call.
A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram
immediately. However, to have multiple exit points in a subprogram is a poor
programming practice.
In procedures, a RETURN statement cannot contain an expression. The statementsimply returns control to the caller before the normal end of the procedure is reached.
However, 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, which acts like a variable of the type specified in the RETURN clause.
An Example follow
FUNCTION calculate_balance (account_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT balance INTO acct_balance FROM accounts
WHERE account_no = account_id;
RETURN account_bal;
END calculate_balance;