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
Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Order of Evaluation When you do not use the parentheses to specify the order of evaluation, the operator precedence determine the order. Now compare the expressions below: NOT

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di