The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN

Exception handling in PL/SQL is a big subject, with a lot of nuances. Still, you have to start somewhere. Let's take one simple use case for exceptions, and see if it leads to some thoughts about best practices. (Hopefully, this is not the last post in this particular series.)

One common pattern I find in PL/SQL procedures is a series of tests early on...

if not_supposed_to_even_be_here() then
 return;
end if;

if no_data_to_process() then
 return;
end if;

if no_parameters_passed() then
 print_basic_page();
 return;
end if;
...

In real life, these tests tend to use hardcoded constants, queries, etc. that clutter up the procedure and make it hard to follow, rather than descriptive names as in the example above. One simple solution is to move the whole block into its own inner procedure:

check_if_supposed_to_be_here();
check_theres_data_to_process();
check_parameters_were_passed();

These procedures, declared with the procedure ... is ... syntax immediately before the begin of the main procedure, can access all the variables from the main procedure, so they typically don't require parameters. In most cases, you can just lift a block of confusing code from the main procedure, and turn it into an inner procedure with a descriptive name.

However, the return statement complicates things. When transplanted into an inner procedure, it loses its mojo. Instead of cutting short the entire procedure, it becomes essentially a no-op, because now it's at the end of a short inner procedure that was about to return anyway, back to the middle of the main procedure. The solution is to use an exception, which requires structuring the whole business like so:

create or replace procedure big_procedure as
  num_rows number;
  exception skip_normal_processing;
  procedure check_data_to_process is
    select count(*) into num_rows from data_table;
    if num_rows = 0 then
      raise skip_normal_processing;
    end if;
  end;
begin
  check_data_to_process();
  ...do all the normal stuff if there really is data to process...
exception
  when skip_normal_processing then null;
end;
/

Now if you detect some condition that means the procedure should bail out, it really will. If you can anticipate that your procedures might get lengthy enough to benefit from using inner procedures this way, you can plan ahead by using exceptions right from the start, instead of starting with return statements and then turning them into exceptions when you restructure the original straight-line procedure.

One thing that still bothers me is the way the control flow jumps around. The calls to the inner procedures jump backwards, and if any "stop! now!" conditions are triggered, control jumps forward all the way to the end of the main procedure. When I visualize such a structure, it reminds me just a little of spaghetti code. I know that on paper, all is as it should be -- all the reusable / modular code is separated out at the front, all the error handling and termination code is separated out at the end. I just would like to see more real-life cases where such structure saves on maintenance and debugging time, before passing final judgment.