Go "native" with Booleans in PL/SQL

This post was inspired by a Twitter conversation doing the Twitter version of shaking heads over the kind of code developers write with Booleans.

Keep it simple and native and intuitive: Booleans are TRUE or FALSE (ok, also maybe NULL). So you don't have to write code like "IF my_boolean = TRUE".

Suppose that I needed to implement a function IS_A_FACT so that I can compile and run the following block:

BEGIN
   IF is_a_fact ('Steven says: The sun revolves around the earth.')
   THEN
      DBMS_OUTPUT.put_line ('Fact!');
   ELSE
      DBMS_OUTPUT.put_line ('Opinion!');
   END IF;
END;

Here are four different ways of getting the job done. They all work. Which would you prefer?

1. Lay it all out there, Steven

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      l_is_a_fact := TRUE;
   ELSE
      l_is_a_fact := FALSE;
   END IF;

   IF (l_is_a_fact = TRUE)
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

2. OK, a bit more concise

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      l_is_a_fact := TRUE;
   ELSE
      l_is_a_fact := FALSE;
   END IF;

   RETURN CASE l_is_a_fact WHEN TRUE THEN TRUE ELSE FALSE END;
END;

3. I'm melting, melting...

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
   l_is_a_fact   BOOLEAN;
BEGIN
   IF statement_in LIKE 'Steven says:%'
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

4. Seriously, this is all I need to write?

FUNCTION is_a_fact (statement_in IN VARCHAR2)
   RETURN BOOLEAN AUTHID DEFINER
IS
BEGIN
   RETURN statement_in LIKE 'Steven says:%';
END;

Yes, quite seriously: that is all you need to write. It's wonderful that PL/SQL supports the Boolean datatype (named after George Boole, "father" of symbolic logic) - and not so wonderful that Oracle SQL still does not. So use it in the most natural, readable way possible!

Oh and by the way, the block of code at the beginning of the post could be changed to a single call to DBMS_OUTPUT.PUT_LINE as follows:

BEGIN
   DBMS_OUTPUT.put_line (
      CASE
         WHEN is_a_fact ('Steven says: The sun revolves around the earth.')
         THEN
            'Fact!'
         ELSE
            'Opinion!'
      END);
END;

Hurray, Booleans and CASE!