Out mode - parameter modes, PL-SQL Programming

Assignment Help:

OUT Mode

An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal parameter as if it were a local variable. You can change its reference or value in any way, as the illustration below shows:

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS

hire_date DATE;

bonus_missing EXCEPTION;

BEGIN

SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp

WHERE empno = emp_id;

IF bonus IS NULL THEN

RAISE bonus_missing;

END IF;

IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN

bonus := bonus + 500;

END IF;

...

EXCEPTION

WHEN bonus_missing THEN

...

END calc_bonus;

The actual parameter which corresponds to an OUT formal parameter should be a variable; it cannot be a constant or an expression. For illustration, the procedure call below is illegal:

calc_bonus(7499, salary + commission); -- causes compilation error

An OUT actual parameter can contain a value before the subprogram is called. Though, the value is lost when you call the subprogram. Similar variables, OUT formal parameters are initialized to NULL. Therefore, the datatype of an OUT formal parameter cannot be a subtype defined as NOT NULL (that involves the built-in subtypes NATURALN and POSITIVEN).

Or else, if you call the subprogram, the PL/SQL raises VALUE_ERROR. An illustration is as shown below:

DECLARE

SUBTYPE Counter IS INTEGER NOT NULL;

rows Counter := 0;

PROCEDURE count_emps (n OUT Counter) IS

BEGIN

SELECT COUNT(*) INTO n FROM emp;

END;

BEGIN

count_emps(rows); -- raises VALUE_ERROR

Before exit a subprogram, it explicitly assigns values to all OUT formal parameters. Or else, the equivalent actual parameters will be null. If you exit effectively, the PL/SQL assigns values to the actual parameters. Though, if you exit with an unhandled exception, the PL/SQL does not assign values to the actual parameters.


Related Discussions:- Out mode - parameter modes

Initializing and referencing collections, Initializing and Referencing Coll...

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)

I want database development with analysis tools, Project Description: I ...

Project Description: I want a database for large governmental and private data sets on one country that will be easily extended to other countries in the future. Also, the datab

Fetching across commits, Fetching Across Commits The FOR UPDATE clause...

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Aggregate assignment-declarations in sql, Aggregate Assignment The %ROWT...

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Semijoin and composition - sql, Semijoin and Composition - SQL For sem...

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Positional and named notation, Positional and Named Notation You can wr...

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

3cx pbx sync windows service, 3CX PBX sync Windows Service Project Descr...

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

Best practices/Data Warhousing, What are 3 good practices of modeling and/o...

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

Union without corresponding - sql, UNION without CORRESPONDING - SQL T...

UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Program, heap sort program in pl/sql

heap sort program in pl/sql

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd