Oreview - PL/SQL Guru

PL/SQL GURU

By Steven Feuerstein

Truly Leveraging Booleans, Part 1

Programming is, with few exceptions, performed by human beings. Human beings tend to live and work in ruts: You figure out ways to get things done and then you rely on those familiar and proven pathways again and again. There is nothing wrong with this approach, except that you can end up missing out on an awful lot of what life (and programming languages) has to offer.

I see this dynamic all the time with PL/SQL developers. You work with the language for a few months and you are fairly certain you know what's there and how to use it. That attitude astounds me, because I have been working intensively with PL/SQL for several years now and I am always learning new tricks.

One of the important lessons I have learned is that you have to do more than understand the syntax and documented features of a language. You have to learn how to fully leverage all of those features. To get that point across and also to offer some tricks and techniques I have picked up, I am going to spend the next two issues of OReview taking a look at Booleans in PL/SQL.

In this issue, I review what a Boolean is and how it can be used in Oracle's procedural language. In the next issue I will move on to how and why you might want to build a layer of code around Booleans to make your life easier and your code cleaner.

What is a Boolean?

A Boolean is a scalar data structure that can have only one of three values: TRUE, FALSE, or NULL. Notice that there are no single quotes around any of those values; they are literal values. Boolean is the "logical" datatype of PL/SQL. Notice that when I refer to the datatype, I capitalize the "B" in Boolean. I do so because the Boolean is named afer George Boole, a mathematician who lived in the first half of the 19th century and is considered the "father of symbolic logic."

Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can be either TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code. With Booleans you can write code that is easily readable because it is more English-like. The example below, chock full of Booleans, should give you a sense of that readability:

CREATE OR REPLACE FUNCTION good_hire (birthdate_in IN DATE, reqsal_in IN NUMBER) RETURN BOOLEAN IS too_young BOOLEAN := ADD_MONTHS (SYSDATE, -216) > birthdate_in; too_expensive BOOLEAN := reqsal_in > fin.dept_bal; BEGIN RETURN NOT (too_young OR too_expensive); END;

This example shows a Boolean function as well as two local Boolean variables inside that function. When you supply a prospective employee's birthdate and requested salary, the function checks to see if an employee is at least 18 years old (18 3 12 = 216) and if there is enough money left in the department budget (a department's budget information is stored or available through the fin package's dept_bal function - or maybe a variable; you cannot tell from the code).

Inside the function, I declare two local variables to hold the values (TRUE, FALSE, or NULL) corresponding to these conditions. As a result, the body of my function is very concise and readable:

RETURN NOT (too_young OR too_expensive);

In other words, this person is a "good hire" if she is not too young or too expensive. If I had not used those "intermediate" Booleans to encapsulate my logic, the body of the function would have been much longer and more difficult to understand. And this is, of course, just a very simple example.

But that is just the beginning. Let's see how this function can be used inside another PL/SQL block (in this case, an Oracle Forms Pre-Insert trigger):

IF good_hire (:newemp.birthdate, :newemp.proposed_sal) THEN SELECT empseq.NEXTVAL INTO :newemp.empno FROM dual; ELSE RAISE FORM_TRIGGER_FAILURE; END IF;

In other words: If the new employee is a good hire, then get the next sequence number, stuff it into the primary key, and continue with the row insert. Otherwise, raise an exception to stop the row insert. Again, the Boolean nature of the function lets it "plug and play" into my code very easily. The result is a very readable chunk of PL/SQL.

To summarize: You can define and use Boolean functions, variables, and constants. Every time you use a Boolean, you encapsulate or hide a (potentially) complicated Boolean expression involving many different variables and tests behind a name that directly expresses the intention and meaning of the text. Although this statement is actually true for any identifier you define in PL/SQL, it has maximum impact with Booleans because they return a logical value and bring you closer to writing code that can be read as if it were a human rather than computer language.

Writing Lean Boolean Code

So use Booleans! Take advantage of their readability, but make sure that you understand fully the way they can work in your programs. Consider the following fragment of PL/SQL code:

IF v_hiredate < SYSDATE THEN date_in_past := TRUE; ELSE date_in_past := FALSE; END IF;

What could be more straightforward? If the v_hiredate comes before the current "system date," then set the date_in_past variable to TRUE; otherwise, set it to FALSE. The basic problem with this code is that it is way too much code. Why not just type the following statement?

date_in_past := v_hiredate < SYSDATE;

In other words, apply the condition directly to the variable. You can assign complex (or simple) Boolean expressions to a Boolean variable. You don't have to rely on the IF statement to then assign the literal values of TRUE or FALSE. Whenever you see that type of conditional logic, check to see if it is really necessary or if it can be replaced with a single line of equivalent code.

But wait! Is that single-line assignment truly equivalent to the IF statement? Actually, it is not equivalent when the v_hiredate variable has a NULL value. With the IF statement, a NULL v_hiredate results in date_in_past being set to FALSE. With the direct assignment, a NULL v_hiredate results in date_in_past being set to NULL. To make these two statements truly identical, I need to add some NULL-value handling:

date_in_past := NVL (v_hiredate, SYSDATE) < SYSDATE;

Now if v_hiredate is NULL, a comparison is made with SYSDATE and, because nothing is less than itself, date_in_past is set to FALSE.

No Booleans in the RDBMS

Just when you are thinking that life with Booleans is great, I have some bad news: The Oracle RDBMS does not support a Boolean datatype. I do not understand why Oracle has not taken this step. But the fact is that you cannot define a column in a table with type BOOLEAN. Instead, you can (must) create a table with a column of datatype CHAR(1) and store either "Y" or "N" in that column to indicate TRUE or FALSE, or create a numeric column and store either 1 or 0 for TRUE or FALSE. That is a poor substitute, however, for a datatype that stores true Boolean values (or NULL).

Because there is no counterpart for the PL/SQL Boolean in the Oracle RDBMS, you can neither SELECT into a Boolean variable nor insert a TRUE or FALSE value directly into a database column. There is, in other words, a disconnect between your code and the database. And any time you have a disconnect, you have a much higher probability of introducing errors into your programs (or your database!).

What's the problem? Well, suppose you have tables that contain True/False columns. You cannot define them as BOOLEAN, so you have to adopt a substitute. Here is your first challenge: No matter which approach you pick, you must ensure that everyone who defines tables and columns in the program takes the same approach. If you are using a product such as Oracle Designer/2000, or if you have tightly controlled who sets up data structures, this may not be a problem. In many organizations, however, there is a whole lot more independence (chaos?), and ensuring consistency can be a real challenge.

But let's just assume that you at least have your act together on the database side. The standard is that you store "Y" for TRUE and "N" or FALSE. Now let's look at the code side of your life. You will want to read from and write to tables with these pseudo-Boolean columns.You have, roughly, two choices:

1. The LCD approach: The database doesn't have Booleans, so you go with the "lowest common denominator" and do not use Boolean variables in your code.

2. The TFA approach: You are committing to taking full advantage of what PL/SQL has to offer. You will use Booleans at every possible opportunity.

I am a strong proponent of the TFA approach. You should maximize your use of PL/SQL so that your code is as readable, maintainable, and bug-free as possible.

Translator Needed!

If you are going the TFA route, you need to find a way to bridge the gap between pseudo-Booleans in the database and real Booleans in PL/SQL. You need a translator that lets you read from and write to the database reliably from within a PL/SQL program. Reliability in this case means:

Programmers can - as transparently as possible - move data between an Oracle database and PL/SQL code, while still taking full advantage of the Boolean datatype in their code.
·Individual developers do not have to know about and remember the conventions established by the DBA to define psuedo-Booleans in the database.

In the context of PL/SQL, this translator is an "API" or programmatic interface to the underlying data structures. Let's look at the different elements we will need in this API.

A Translator API

By providing a programmatic interface, we can guarantee consistency and quality of both process and data. Following are the types of actions I want my API to support:

Define the value or values that are to be recognized as representing TRUE, FALSE, and NULL in the database. For example, I might want "F" to represent FALSE or I might even want to allow 0, "f," "F," and "N" to all represent FALSE.
Retrieve the value or values that represent the literal Boolean values. I need functions to return the curent value(s) for use in comparison or in passing into the Oracle database.
Convert a Boolean value to a string or numeric value. This is the translation step needed to move from PL/SQL code to Oracle database table.
Convert a string or numeric value into a Boolean value. This is the translation step needed to move from an Oracle database table to PL/SQL code.

With these programmatic pieces in place, I can move smoothly from native Boolean values in my PL/SQL programs to consistent pseudo-Booleans in my database tables.

Building the Translator

In my May column, I will show you how to implement the translator I've outlined here. While you are waiting anxiously to see this code, I suggest that you give it a try yourself. How would you build an API to pseudo-Booleans and ensure consistency of values (hint: use a package)?

Steven Feuerstein is the author of Advanced Oracle PL/SQL: Programming with Packages (O'Reilly & Associates, 1996) and is a senior technology officer for RevealNet (www.revealnet.com). You can reach Steven via email at feuerstein@revealnet.com.