Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Go Native With Booleans in PL/SQL

DZone's Guide to

Go Native With Booleans in PL/SQL

The kind of code developers who write with Booleans are a kind of their own. In this article, we'll see how to keep things as simple as possible in PL/SQL.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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!

Do you pay to use your database? What if your database paid you? Learn more with RavenDB.

Topics:
database ,plsql ,booleans

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}