in,out,in out

Specifying Subprogram Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes are IN (the default), OUT, and IN OUT.

Any parameter mode can be used with any subprogram. Avoid using the OUT and IN OUT modes with functions. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Using the IN Mode

An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value.

You can pass a constant, literal, initialized variable, or expression as an IN parameter.

IN parameters can be initialized to default values, which are used if those parameters are omitted from the subprogram call. For more information, see "Using Default Values for Subprogram Parameters".

<!-- class="sect3" -->

Using the OUT Mode

An OUT parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. You can change its value, and reference the value after assigning it:

Example 8-6 Using the OUT Mode

DECLARE
  emp_num       NUMBER(6) := 120;
  bonus         NUMBER(6) := 50;
  emp_last_name VARCHAR2(25);
  PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER, 
                          emp_name OUT VARCHAR2) IS
    BEGIN
      UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
      SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  raise_salary(emp_num, bonus, emp_last_name); 
  DBMS_OUTPUT.PUT_LINE('Salary has been updated for: ' || emp_last_name);
END;
/

<!-- class="example" -->

You must pass a variable, not a constant or an expression, to an OUT parameter. Its previous value is lost unless you specify the NOCOPY keyword or the subprogram exits with an unhandled exception. See "Using Default Values for Subprogram Parameters".

Like variables, OUT formal parameters are initialized to NULL. The datatype of an OUT formal parameter cannot be a subtype defined as NOT NULL, such as the built-in subtypes NATURALN and POSITIVEN. Otherwise, when you call the subprogram, PL/SQL raises VALUE_ERROR.

Before exiting a subprogram, assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

<!-- class="sect3" -->

Using the IN OUT Mode

An IN OUT parameter passes initial values to a subprogram and returns updated values to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT parameter is a string buffer or numeric accumulator, that is read inside the subprogram and then updated.

The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression.

If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

<!-- class="sect3" -->

Summary of Subprogram Parameter Modes

Table 8-1 summarizes all you need to know about the parameter modes.

Table 8-1 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 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
Actual parameter is passed by reference (a pointer to the value is passed in)	Actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified	Actual parameter is passed by value (a copy of the value is passed in and out) unless NOCOPY is specified