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 Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Adding Table Constraints ALTER TABLE ENROLMENT ADD CONSTRAINT NameNotNull CHECK (Name IS NOT NULL) ; ALTER TABLE ENROLMENT ADD CONSTRAINT PK_StudentId_CourseId PRIM

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

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

Keyword and Parameter Description: label_name: This is an undeclared identifier which optionally labels the PL/SQL block. When used, label_name should be enclosed by the do

what is the use of declare keyword