Up, Periscope!

Last year I "inherited" the maintenance of some our shop's old PL/SQL code.  It was a key piece of infrastructure code; it was also over 15 years old, and many hands had worked on the code before me.  There was some interesting functionality with which I was not familiar, so before I made changes to the code, I needed to take a deeper dive into the code. 

After 15 years and many hands working on the code, I found the scope of the variables to be very confusing.  Variables were global; variables were public; variables were local to a function or a procedure or an anonymous block.  Some variables were scalars, others were part of a structure.  Some variables belonged to the same domain; they were really duplicates of each other.  There were local and global variables with the same name, and some of the code just assigned values back and forth.

So, for year, I maintained the code as it was, doing what I needed to do to add functionality as required, trying to make as few changes as possible to not break something. 

Now I have a new mandate:  retire the old code and write a new, simpler version.  There's a lot of good work in the old code, so I'm not going to throw the baby out with the bath water.  But I am taking a deeper dive into the scope of the variables, and that leads to this post's topic, taking a deep dive into variable scope and variable lifetime.  We'll take a look at private versus public variables, local versus global variables, and why we might choose one over the other.  Our examples are in Oracle's PL/SQL programming language, but it's important to understand variable scope and variable lifetime in any programming language.

Let's start with a package specification:

create or replace package demo_scope as

global_public_string varchar2(32);

procedure test (
   p_arg1 in varchar2 default null,
   p_arg2 in out number   default null);

function test_func (
    p_arg3 in number )
  return varchar2;

end demo_scope;

Our package has one procedure, one function, and one varchar2 string visible to any user with execute permission on the package. Based on the package specification, a valid anonymous block could look like the following:

declare
   my_func_results varchar2;
   my_number number := 1776 ;
begin
   demo_scope.global_public_string := 'my value';

   demo_scope.test(p_arg1 => 'argument to test', 
                   p_arg2 => my_number);

   my_func_results := demo_scope.test_func(2001);
end; 

Our package body implements the public functions and procedures. In addition, the package body defines more variables, functions, and procedures that are not visible to package users. Here's our package body:

create or replace package body  demo_scope as

global_private_string varchar2(32);

procedure test (
   p_arg1 in varchar2 default null,
   p_arg2 in out number   default null) is
    
   local_private_string varchar2(32);

begin
     
    -- An anonymous block within a named procedure
    declare
    
       local_private_in_block_string varchar2(32);

    begin
    
       global_public_string := p_arg1;
       global_private_string := 'some other value';

    end;
    
    p_arg2 := 1976;

end test;
 
function test_func ( p_arg3 in number )
    return varchar2 is

begin

  return null;

end test_func;

begin  --Package initialzation

  global_public_string := null;
  global_private_string := null;  

end demo_scope;

Let's take a look at the variables and see what we can do with them. We'll start from the package specification and work down through the package body.

1. Variable global_public_string -- Any user of the package and any block within the package can directly reference this variable.  Declaring  variables (functions and procedures, too) in the package spec instead of the package body makes them public.
2. Procedure test -- Procedure test is public, and any user of the package can call this procedure.
3. Parameters p_arg1 and p_arg2 -- Any user calling procedure test can supply arguments for these parameters..  Parameter p_arg1 is passed by value to the procedure. Because p_arg2 is specified as an "out" parameter, procedure test can change p_arg2 and the new value is returned to the caller.  If we try to assign a value to parameter p_arg1 within procedure test, we will get an error message when we try to compile the procedure.
4. Function test_func -- Any user of the package can call this function.
5. Parameter p_arg3 -- Any user calling function test_func can supply an argument for this parameter.
6. Variable global_private_string -- Only functions, procedures, and blocks defined within the package body can use this variable.  Declaring variables outside of functions and procedures in a package body makes the variable scope global.  Declaring variables in the package body rather than the package spec makes them private; only functions and procedures defined in the package can reference these variables.
7. Variable local_private_string -- Only the procedure test and any blocks within procedure test can use this variable. When procedure test ends, the value of this variable is lost.
8. Variable local_private_in_block -- Only the anonymous block within procedure test can use this variable. When this block ends, the value is lost. This variable can not be referenced outside of the block.

To review: locally-defined variables do not retain their value when the block (procedure, function, or anonymous block) ends.   Locally-defined variables can only be referenced within the block that declares them.

Parameters passed to functions and procedures can not be changed by the function or procedure unless the parameter is an "out" parameter.  The PL/SQL compiler flags as an error any attempt to assign a value to an "in" parameter.

Global variables declared in the package body can be referenced by any block defined in the package, but they can not be referenced outside the package body.  Global variables declared in the package spec are public and can be referenced anywhere in the package body, and they can be referenced by any user of the package.

Global variables retain their values when a block ends.   In fact, a package's  global variables retain their values as long as the session is active.  Control can pass from the package back to its caller;  global variables still retain their value.

This property makes them useful for caching results that may be expensive to compute.  Repeatedly selecting data from a lookup table can often be done more inexpensively by defining an associative array as a global variable,  loading data into the array during package initialization, and then searching the array by a key.  This can be a big performance gain when lookups are performed in a tight loop.  And as long as the database session is active, the array retains its values, and there is no need to reload it.

Good programming practice dictates that we define variables in the scope that we need them.  It may be tempting to define variables globally, so that "we'll have them when we need them".  Over-use of globally-defined variables makes a program difficult to understand.  But used carefully, global variables can be a useful tool for improving program performance. 


One last thought:  Have a Happy New Year, and may all your code be bug-free!