Feuertip #17: Named notation rocks!

OK, so you write procedures and functions. Almost all those programs have one or more parameters. And so, finally, we’ve gotten to the point of this post:

How much do I love named notation? Let me count the ways.

=> 1. It makes my code more readable.
=> 2. It makes it easier to modify my code over time.
=> 3. It’s something you can do in PL/SQL that you cannot do in several other popular languages.

Aren’t I clever with the way I worked “=>” into my list?

Because that’s how you get to named notation.

Where can you use named notation?

1. Associating argument values with formal parameters (in PL/SQL and SQL!)
2. Associating an expression with an associative array index value (qualified expression)

Let’s look at an example.

create or replace procedure calculate_annual_carbon_footprint (
   p_miles_driven  in   number,
   p_car_type      in   varchar2,
   p_diet_type     in   varchar2,
   p_miles_flown   in   number,
   p_description   out  varchar2,
   p_co2_out       out  number
) is
begin
   /* Sorry I do not have the expertise to do the calculation! */
   p_co2_out      := 1000;
   p_description  := 'Almost certainly too much if you''re lucky enough to live comfortably.';
end;
/

With positional notation…

declare
   l_desc  varchar2(1000);
   l_co2   number;
begin
   calculate_annual_carbon_footprint (
      10000,
      'electric',
      'vegetarian',
      5000,
      l_desc,
      l_co2
   );
end;
/

The above code compiles, but wow, it is hard to make much sense of it. Some of the values make it pretty clear what is (likely) involved, like “vegetarian” for diet. But you will likely have to go look at the header of the procedure to understand what’s going on.

With named notation…

declare
   l_desc  varchar2(1000);
   l_co2   number;
begin
   calculate_annual_carbon_footprint (
      p_miles_driven  => 10000,
      p_car_type      => 'electric',
      p_diet_type     => 'vegetarian',
      p_miles_flown   => 5000,
      p_description   => l_desc,
      p_co2_out       => l_co2
   );
end;
/

Now, gee, well, there is no doubt. I know exactly which formal parameter goes with which actual value. Sure, I might need to go check out the procedure definition sometimes to see what it’s doing (or fix a bug). In terms of its use in this block, however, all is clear.

Things to remember about named notation

• Mix named and positional notation together in a single invocation
• Change the order of the parameters when invoked (order/position is not important anymore)
• Once you use named, you cannot go back to positional (you lost the position!)
• Skip over “internal” parameters with default values (something you absolutely cannot do with positional notation)
• If all invocations are with named notation, you can change the order of parameters in the definition of the program, and it won’t cause any problems.
• Works with built-in procedures and functions, too!

The bottom line is that, really, we should always use named notation when invoking subprograms. Some IDEs, like SQL Developer, will help you in this regard, generating the invocation with a click or two. But if you have to do it yourself, it’s usually not all that difficult. Of course, it’s more work the more parameters there are. But the more parameters there are, the more benefit you will get from named notation.

If you can’t bring yourself to do it all the time, though, make very sure you use it for procedures that are rarely called (no one needs to use named notation for dbms_output.put_line or substr!). Anyone working on this code in the future will thank you.

Dive Deeper

For fuller coverage of named notation: https://stevenfeuersteinonplsql.blogspot.com/2019/08/use-named-notation-for-crystal-clear.html

For a LiveSQL script with all the details: https://livesql.oracle.com/apex/livesql/file/content_CJGNZFI9Y0K8HFO6KXJYG8IQB.html

A handy table on Positional, Named, and Mixed Notation for Actual Parameters from Oracle Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-subprograms.html#GUID-A5DA8CF5-1BCC-4ABE-9B68-DB593FF1D2CC

Oracle Dev Gym quizzes on named notation: https://devgym.oracle.com/pls/apex/f?p=10001:SEARCH:945190192771::::P1202_SEARCH:named%20notation