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:

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.

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
Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Initial thought process: Design a script which was simple and user friendly. Integrate procedures/functions to extract data under the hood. I focused on giving the user the opt

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b