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
Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

%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

Using a Host Variable You can declare the cursor variable in the PL/SQL host environment like an OCI or Pro C program. To use the cursor variable, you should pass it as a host

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_