 |
|
Oracle Tips by Burleson |
IN, OUT and INOUT Modes
Values are passed to a procedure in three modes;
IN, OUT and INOUT. The mode which a variable is passed defines how
the variables can be used inside the procedure. Let’s take a closer
look at each mode.
IN Mode
A variable passed as mode IN is always read-only.
A variable using IN mode can be read and used by the
procedure/function but can not be changed and it cannot be the
receiver of an assignment operation. Internal to the scope of the
procedure or function, variables pass using IN mode can be considered
a constant. The IN mode is the default mode to pass a variable,
however it is recommended for maintainability reasons to always define
the variable passing mode when you define the variable. Variables
passed IN can also be assigned a default value as discussed above.
In the example below, three variables are defined
as IN variables. Note that on line 7 the code attempts to assign the
variable n_1 the sum of the other two variables. This
procedure fails on compile because n_1 was assigned a mode of
IN and therefore can not be used in an assignment.
SQL> create
or replace procedure example_defaults
2 (n_1 in number := 5,
3 n_2 in number := 6,
4 n_3 in number := 7)
5 as
6 begin
7 n_1 := n_2 + n_3;
8 end;
9 /
Warning:
Procedure created with compilation errors.
SQL> show
errors
Errors for PROCEDURE EXAMPLE_DEFAULTS:
LINE/COL
ERROR
-------- ------------------------------------------------7/3
PLS-00363: expression 'N_1' cannot be used as an
assignment target
7/3
PL/SQL: Statement ignored
OUT Mode
A variable passed in OUT mode is used
to pass information back from the procedure to the calling program.
It is a write-only variable and has no value until the block assigns
it a value. Internally, an OUT variable is created and not
initialized when the procedure is called. When the procedure ends,
the variable value (upon ending) is copied to the variable passed in
the call. As such, a variable passed in OUT mode can not be assigned
a default value nor can it be read inside the procedure. Because the
variable value is copied back to the passed variable when the
procedure terminates, the calling code can not pass an OUT variable a
literal value. If the procedure raises an exception that is not
caught, it will result in the OUT variable not being copied when the
procedure terminates.
SQL> create
or replace procedure example_defaults
2 (n_1 in number := 5,
3 n_2 in number := 6,
4 n_3 out number := 7)
5 as
6 begin
7 null;
8 end;
9 /
Warning:
Procedure created with compilation errors.
SQL> show
errors
Errors for PROCEDURE EXAMPLE_DEFAULTS:
LINE/COL
ERROR
-------- -------------------------------------------
4/4 PLS-00230: OUT and IN OUT formal parameters may not have
default expressions
INOUT Mode
A variable passed in INOUT mode has
characteristics of both the IN and the OUT mode. The
variable value is passed in and can be read by the procedure. The
procedure can also change the value and it will be copied back to the
passed variable when the procedure completes. Like a variable passed
in OUT mode, an INOUT variable can not have a default value and can
not be passed as a literal. If the procedure terminates abnormally
(as in an exception) the INOUT variable will not be copied back to the
variable passed in.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
