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.