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!

Comments

  1. Stephen,
    You're truly mastering writing good examples.
    Great example of refactoring towards simple and functional code.

    ReplyDelete
    Replies
    1. You are very kind, Jacek. I will send you the check for payment for this praise next Tuesday. :-)

      Delete
  2. I think a lot of developers assume that PL/SQL doesn't have a Boolean data type because SQL doesn't. I see a lot of PL/SQL code where a char(1) is declared and the value is set to 'Y' or 'N' instead of using a Boolean (obviously this may be necessary in some situations such as if it's needed with a SQL statement).

    ReplyDelete
  3. That's interesting, never thought of that. As you say, we still do it because the data is going into or coming out of a SQL pseudo-Boolean column.

    Well, I hope my blog post and similar quizzes at the Oracle Dev Gym (devgym.oracle.com) can help inform developers on this topic!

    Do you have any other thoughts of how we might move people away from this poor practice?

    ReplyDelete
  4. I myself use the C type booleans (a numeric value; 0=false, 1=true) often because of 2 reasons

    - There is no boolean data type in SQL. Sometimes I need to store them in a Table however, and reference them in PL/SQL. Also more often than not I have functions returning booleans which afer a while need to be invoked from SQL for whatever reason. Well, either I write a SQL wrapper for the boolean function, or I simply write it as numeric in the first place if I can estimate it'll be used in SQL soon.
    - At least in JDBC up until 12.1 boolean isn't supported (If memory serves it'll be supported from 12.1 onward, but I am not 100% certain here).
    As for now until extended support for 11.2 ends at least I am stuck with 11.2.
    Suppose I want to pass a boolean from java to PL/SQL. I either convert the java boolean to a numeric value in java, pass it to a stored procedure and in PL/SQL convert it back to boolean or I simply use the C type booleans. Btw. if I need to pass the boolean further to SQL I need to convert it back to C Type booleans *again* (and as for now there is no way around that even with 12.2). Or I skip the whole boolean => numeric conversion tragedy alltogether and use my own implementation of the boolean datatype which can be used from java via PL/SQL down to SQL.

    Bottom Line: unless I can't pass a boolean datatype from the client language (e.g. java) to the server (PL/SQL) to the SQL Engine the boolean datatype in PL/SQL is nice, but somehow (sorry to be blunt) often useless. More often than not I cannot use it even though I want to.
    And until you don't have to do back and forth conversions from boolean to something else because one part of the database supports them (PL/SQL) while the other doesn't (SQL) you'll see more or less elegant custom implementations of the boolean datatype (maybe they reflect the language they started learning programming - yes, I started with C ;-)).

    cheers

    ReplyDelete
  5. Thanks, Christian. Your points are very convincing. I wish we did offer a smoother path all along the stack.

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts