Go Native With Booleans in PL/SQL
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.
Join the DZone community and get the full member experience.
Join For FreeRavenDB 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?
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;
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;
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;
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;
Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.
Published at DZone with permission of Steven Feuerstein , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}