 |
|
PL/SQL Notations
Oracle Tips by
Burleson
|
PL/SQL supports two types of notation when calling procedures with
parameters: named notation and positional notation.
Named Notation
Named notation is calling a stored procedure by specifying
both the parameter names and each parameter’s value. Listing 4.13
illustrates the use of named notation when calling a stored procedure.
Listing 4.13 Calling a procedure using named notation.
Check_Source_For_Insert (vOwner => 'JSCHMOE',
vObject => 'CALCULATE_GPA');
When using named notation, the parameters for a procedure can be
specified in any order (after all, the developer knows which values
are intended for which parameters). Obviously, the use of named
notation requires that the developer knows the names of the parameters
and specifies the parameter names in procedure calls.
Positional Notation
Positional notation is calling a stored procedure by simply
passing parameter values and assuming that the values will be
associated with parameters in the order of declaration. The first
value passed is associated with the first parameter, the second value
with the second parameter, and so forth. Listing 4.14 illustrates a
call to a stored procedure using positional notation.
Listing 4.14 Calling a stored procedure using positional
notation.
Check_Source_For_Insert ('JSCHMOE',
'CALCULATE_GPA');
When using positional notation, the values must be passed to the
stored procedure in the proper order. This requires that developers
know the order of the parameters.
Mixing Notations
It’s possible to use both named notation and positional notation in
a single call to a stored procedure, as shown in Listing 4.15.
Listing 4.15 Mixing named and positional notation.
Approve_For_Credit (999999999,
9032012912,
'C',
iBounced_Checks => 0,
iOverdrafts => 0);
When mixing notation like this, it’s important to keep in mind that
positional notation can be used only before named notation has been
used. If even one parameter is populated by using named notation, all
subsequent parameters must also be populated using named notation.
Named Or
Positional Notation?
Consider again the code in Listing 4.15. While you may have figured
out that the first parameter is a social security number, you probably
have no idea what the second and third parameters are supposed to
represent. If the code had used named notation (or if variables were
being passed instead of literal values), you would be able to see that
the second parameter holds an account number and the third parameter
indicates whether the account is a checking or savings account.
I prefer the use of named
notation when calling procedures and functions. While somewhat more
work is involved during development, it’s much clearer down the road
to see what values are being passed to which parameters. And when
combined with meaningfully named variables, named notation contributes
a great deal to making the code self-documenting.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
