Use named notation for crystal clear code

The first and most important criteria of high quality code is that it meets user requirements (it's "correct").

The second most important criteria is that it fast enough to meet user needs. It doesn't matter that you might be able to make it go faster.

The third most important criteria of excellent code is that it is maintainable. Why, you might be wondering, is maintainabiliity so important?

Most of the code we write, especially code that lives in the database, sticks around for a long time. Well past the point when you were familiar with the code you wrote. And likely past the time when you are still working on that same code or project.

So the overall cost of development of that code is not just the initial build expense, but also must include the cost of maintaining that code. All too often we are so absorbed in meeting deadlines that we sacrifice clarity and readability to "get the job done."

And then we - or others - pay the price later. Conversely, if we take advantage of features in PL/SQL that enhance readability of code, the burden of maintaining it later will be reduced. Hey, it might even make it easier for you to debug your code today!

With named notation, you explicitly associate the formal parameter (the name of the parameter) with the actual parameter (the value of the parameter) right in the call to the program, using the combination symbol =>.

The general syntax for named notation is:

formal_parameter_name => argument_value 

Because you provide the name of the formal parameter explicitly, PL/SQL no longer needs to rely on the order of the parameters to make the association from actual to formal. So, if you use named notation, you do not need to list the parameters in your call to the program in the same order as the formal parameters in the header.

Suppose I define a function as follows:

FUNCTION total_sales
   (company_id_in IN company.company_id%TYPE,
    status_in IN order.status_code%TYPE := NULL)
RETURN std_types.dollar_amount;

Then I can call total_sales in either of these two ways:

new_sales :=
   total_sales (company_id_in => order_pkg.company_id, status_in =>'N');

 new_sales :=
   total_sales (status_in =>'N', company_id_in => order_pkg.company_id);

It is also possible to mix named and positional notation in the same program call, as in:

l_new_sales := total_sales (order_pkg.company_id, status_in =>'N');

If you do mix notation, however, you must list all of your positional parameters before any named notation parameters, as shown in the above code. After all, positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter. If you place named notation parameters in front of positional notation, PL/SQL loses its place.

Both of the following calls to total_sales will fail. The first statement fails because the named notation comes first. The second fails because positional notation is used, but the parameters are in the wrong order. PL/SQL will try to convert ‘N’ to a NUMBER (for company_id):

l_new_sales := total_sales (company_id_in => order_pkg.company_id, 'N');
l_new_sales := total_sales ('N', company_id_in => order_pkg.company_id);

Benefits of named notation

Now that you are aware of the different ways to notate the order and association of parameters, you might be wondering why you would ever use named notation. Here are two possibilities:

1. Named notation is self-documenting

When you use named notation, the call to the program clearly describes the formal parameter to which the actual parameter is assigned. The names of formal parameters can and should be designed so that their purpose is self-explanatory. In a way, the descriptive aspect of named notation is another form of program documentation. If you are not familiar with all of the modules called by an application, the listing of the formal parameters helps reinforce your understanding of a particular program call. In some development environments, the standard for parameter notation is named notation for just this reason. This is especially true when the formal parameters are named following the convention of appending the passing mode as the last token. Then, the direction of data can be clearly seen simply by investigating the procedure or function call.

2. Named notation gives you complete flexibility over parameter specification

You can list the parameters in any order you want. (This does not mean, however, that you should randomly order your arguments when you call a program!) You can also include only the parameters you want or need in the parameter list. Complex applications may at times require procedures with literally dozens of parameters. Any parameter with a default value can be left out of the call to the procedure. Using named notation, the developer can use the procedure by passing only the values needed for that usage.

Let’s see how these benefits can be applied. Consider the following program header:

PROCEDURE so_many_parameters (
   advertising_budget_in   IN     NUMBER
 , contributions_inout     IN OUT NUMBER
 , start_date_in           IN     DATE DEFAULT SYSDATE
 , bonus_out                  OUT NUMBER
 , flags_in                IN     VARCHAR2 DEFAULT 'WHENEVER POSSIBLE'
);

An analysis of the parameter list yields these observations:

• The minimum number of arguments that must be passed to so_many_parameters is three. To determine this, add the number of IN parameters without default values to the number of OUT or IN OUT parameters.
• I can call this program with positional notation with either four or five arguments, because the last parameter has mode IN with a default value.
• You will need at least two variables to hold the values returned by the OUT and IN OUT parameters.
• Given this parameter list, there are a number of ways that you can call this program:

All positional notation, all actual parameters specified. Notice how difficult it is to recall the parameter (and significance) of each of these values.

DECLARE
   l_max_bonus       NUMBER;
   l_mucho_dollars   NUMBER := 100000;
BEGIN
   /* All positional notation */
   so_many_parameters (50000000
                    , l_mucho_dollars
                    , SYSDATE + 20
                    , l_max_bonus
                    , 'PAY OFF OSHA'
                     );

All positional notation, minimum number of actual parameters specified. Still hard to understand.

so_many_parameters (50000000
                , l_mucho_dollars
                , SYSDATE + 20
                , l_max_bonus
                 );

All named notation, keeping the original order intact. Now my call to so_many_parameters is self-documenting.
so_many_parameters

so_many_parameters
  (advertising_budget_in  => 50000000
 , contributions_inout    => l_mucho_dollars
 , start_date_in          => SYSDATE
 , bonus_out              => l_max_bonus
 , flags_in               => 'autonomous,plsql,sql'
  );

Skip over all IN parameters with default values, another critical feature of named notation:

so_many_parameters
   (advertising_budget_in  => 50000000
  , contributions_inout    => l_mucho_dollars
  , bonus_out              => l_max_bonus
  );

Change the order in which actual parameters are specified with named notation; also provide just a partial list:

so_many_parameters
  (bonus_out             => l_max_bonus
 , start_date_in         => SYSDATE
 , advertising_budget_in => 50000000
 , contributions_inout   => l_mucho_dollars
  );

Blend positional and named notation. You can start with positional, but once you switch to named notation, you can’t go back to positional.

so_many_parameters
   (50000000
   , l_mucho_dollars
   , start_date_in  => SYSDATE
   , bonus_out      => l_max_bonus
    );

As you can see, there is a lot of flexibility when it comes to passing arguments to a parameter list in PL/SQL. As a general rule, named notation is the best way to write code that is readable and more easily maintained. You just have to take the time to look up and write the parameter names.

Resources

Demonstration of Named Notation -my Oracle LiveSQL script

Calling Notation For PL/SQL Subroutines by Manish Sharma

Thanks, O'Reilly Media!

Portions of this post have been lifted (gently, carefully, lovingly) from Oracle PL/SQL Programming, with permission from O'Reilly Media.