Return statement, PL-SQL Programming

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:


acct_bal REAL;


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,



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.

Posted Date: 10/5/2012 5:24:59 AM | Location : United States

Related Discussions:- Return statement, Assignment Help, Ask Question on Return statement, Get Answer, Expert's Help, Return statement Discussions

Write discussion on Return statement
Your posts are moderated
Related Questions
Need Database Development with Analysis Tools Project Description: I want a database for large governmental and private data sets on one country that can be simply extended t

Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=

Defining and Declaring Collections To create the collections, you must define a collection type, and then declare the collections of that type. You can define the VARRAY types a

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

What are 3 good practices of modeling and/or implementing data warehouses?

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts