The Humble IF Statement

If I have one piece of wisdom to impart, it's this: pay attention to your IF statements!

Seems like the hardest part of knowing a dozen languages is keeping track of the different syntaxes for simple IF statements. (I took a tour of an Internet startup pre-dotcom crash, and saw my PL/SQL User's Guide open on a desk -- not to BULK COLLECT or the exception model, but to the page for the IF statement.)

PL/SQL is relatively verbose but straightforward: IF this THEN that END IF;

Python is a little more concise, leaving aside the line breaks and indentation:

if this:
  that
elif the_other:
  something_else

I could wish for a little more Perl-style forgiveness; I keep leaving "then" in by mistake.

My simultaneous favourite and least-favourite IF syntax is from Korn shell:

if [ this ]; then
  that;
fi;

"then" is its own insignificant statement, and so requires the semicolon after the condition to format the lines the intuitive way. The conditions are a weird mix of UNIX-style flags (-f "$filename" to check for file existence) and FORTRAN-style comparison operators ("$string1" eq "$string2").

I can never remember whether the semicolon is needed, or the details of the comparison operators, or sometimes even whether the condition is enclosed in [ ] or [[ ]]. Don't forget that spaces are needed after the [ and before the ], it's an error otherwise. So for me, ksh is the toughest language to switch back into on a moment's notice.

Now this post isn't just a language comparison. There are some shortcuts you can do with IF logic in PL/SQL.

If you find yourself writing statements like:

if x is not null then
  y := x;
else
  y := 'some default value';
end if;

...you can use the NVL() function instead:

y := nvl(x,'some default value');

Because this is a SQL function too, you can use it in queries the same way:

select name, course, nvl(grade,'Incomplete') grade from coursework;

For any row where GRADE is null, the value that comes back will be 'Incomplete'. Since we're plugging a function in instead of a column value, the trailing "grade" is helpful to give a name to the expression value, in case we wanted to wrap the query into a nested query or implicit FOR loop in PL/SQL.

The next level of IF is the CASE expression. This lets you replace a whole sequence of IF/THEN/ELSE clauses with a single expression:

letter := case
  when grade between 85 and 100 then 'A'
  when grade between 75 and 84 then 'B'
  else 'Kiss the honor roll goodbye'
end;

Old-time PL/SQL programmers might be in the habit of doing:

select nvl(something,something_else) into variable from dual;

as a way to do this kind of IF-NULL-THEN assignment for a single value. You can get rid of the query by using NVL in a regular PL/SQL assignment, or (my preference) turning the NVL into a CASE.

It's not the compactness that makes CASE a win for me, it's the fact that (as an expression) you can use it in the middle of some other statement where normally it would break up the flow of thought or require you to create dummy variables to hold intermediate values:

declare
  something varchar2(32000) :=
    case
      when release = 1 then 'value for release 1'
      when release = 2 then 'value for release 2'
      else 'some other value'
    end;

  procedure my_proc
  (
    param1 varchar2 := 'xyz',
    param2 varchar2 := 'abc',
    param3 number := case when to_char(sysdate,'Day') = 'Tuesday' then 7 else 2 end
  ) is ...

PL/SQL's CASE comes in 2 forms, the expression that you can plug in anywhere as shown above, and the statement that must stand alone and ends with END CASE instead of just END:

case
  when percent_full < 75 then
    perform_routine_maintenance();
  when percent_full between 75 and 99 then
    stop_nonessential_jobs();
    send_email_alert();
  when percent_full = 100 or cpu_too_high = true then
    panic_in_the_streets();
  else
    dbms_output.put_line('Some anomalous condition!');
end case;

The statement format lets you do multiple things in response to any condition, and you can test different variables in different WHEN clauses. You aren't just returning a value, but running full statements in each WHEN block. I don't use the statement format very often though. I find it is a little confusing to distinguish between the two forms (usually by checking whether the last part is END or END CASE) when reading existing source. And if you don't properly account for all possibilities in a CASE statement and execution falls off the end, you get a CASE_NOT_FOUND exception, so it requires extra care in constructing the conditions to always match one of the WHEN conditions. (You can trap the exception, but it can be a shock the first time you get one when you hadn't intended the CASE statement to cover every possibility.)