Out mode - parameter modes, PL-SQL Programming

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.

Posted Date: 10/5/2012 5:32:14 AM | Location : United States







Related Discussions:- Out mode - parameter modes, Assignment Help, Ask Question on Out mode - parameter modes, Get Answer, Expert's Help, Out mode - parameter modes Discussions

Write discussion on Out mode - parameter modes
Your posts are moderated
Related Questions
Cursor FOR Loops In most cases that need an explicit cursor, you can simplify the coding by using a cursor FOR loop rather of the OPEN, FETCH, and CLOSE statements. A cursor FO

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT