PL/SQL User's Guide and Reference

Contents Index Home Previous Next

Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments (actual parameters) and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

IN Mode

An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following assignment statement causes a compilation error: 

PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS
   minimum_purchase CONSTANT REAL := 10.0;
   service_charge   CONSTANT REAL := 0.50;
BEGIN
   ...
   IF amount < minimum_purchase THEN
      amount := amount + service_charge;  -- causes syntax error
   END IF;

The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression.

Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Parameter Default Values" [*].

OUT Mode

An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like an uninitialized variable. Therefore, its value cannot be assigned to another variable or reassigned to itself. For instance, the following assignment statement causes a compilation error: 

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS
   hire_date DATE;
BEGIN
   SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
      WHERE empno = emp_id;
   IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
      bonus := bonus + 500;  -- causes syntax error
   END IF;

The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or an expression. For example, the following procedure call is illegal:

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

An OUT actual parameter can have a value before the subprogram is called. However, the value is lost when you call the subprogram. Inside the subprogram, an OUT formal parameter cannot be used in an expression; the only operation allowed on the parameter is to assign it a value.

Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the values of corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

IN OUT Mode

An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable. That means you can use an IN OUT formal parameter as if it were a normal variable. You can change its value or reference the value in any way, as the following example shows: 

PROCEDURE calc_bonus (emp_id IN INTEGER, bonus IN 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 that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression. Table 7 - 1 summarizes all you need to know about the parameter modes.

IN OUT IN OUT
the default must be specified must be specified
passes values to a subprogram returns values to the caller passes initial values to a subprogram and returns updated values to the caller
formal parameter acts like a constant formal parameter acts like an uninitialized variable formal parameter acts like an initialized variable
formal parameter cannot be assigned a value formal parameter cannot be used in an expression and must be assigned a value formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression actual parameter must be a variable actual parameter must be a variable
Table 7 - 1. Parameter Modes

Contents Index Home Previous Next