Oracle PL/SQL Best Practices

Name

CTL-01: Use ELSIF with mutually exclusive clauses.

Synopsis

When you need to write conditional logic that has several mutually exclusive clauses (in other words, if one clause is TRUE, no other clause evaluates to TRUE), use the ELSIF construct:

IF condA THEN
   ...
ELSIF condB THEN
   ...
ELSIF condN THEN
   ...
ELSE
   ...
END IF; 

Example

At first glance, the following procedure makes sense, but on closer examination, it’s a mess:

PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   END IF;
   IF line_in = 2
   THEN
      process_line2;
   END IF;
   ...
   IF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;

Every IF statement is executed and each condition evaluated. You should rewrite such logic as follows:

PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   ELSIF line_in = 2
   THEN
      process_line2;
   ...
   ELSIF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;

Benefits

This structure clearly expresses the underlying “reality” of your business logic: if one condition is TRUE, no others can be TRUE.

ELSIF offers the most efficient implementation for processing mutually exclusive clauses. When one clause evaluates to TRUE, all subsequent clauses are ignored.