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
Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

What are the rates for help in writing PL/SQL procedures and functions?

Using Operator DEREF: You cannot navigate through refs within the PL/SQL procedural statements. Rather than, you should use the operator DEREF in the SQL statement. The DEREF

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s