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
Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security l