In this chapter, we'll explore all the native PL/SQL datatypes that we haven't yet covered. These include the BOOLEAN and UROWID/ROWID types, as well as the LOB (large object) family of types. We'll also discuss some useful, predefined object types, including XMLType, which enables you to store XML data in a database column, and ANYDATA type, which allows you to store, well, just about anything.
12.1 The BOOLEAN Datatype
the Oracle RDBMS/SQL language offers features not found in PL/SQL, such as the Oracle SQL DECODE construct. PL/SQL, on the other hand, has a few tricks up its sleeve that are unavailable in native SQL. One particularly pleasant example of this is the BOOLEAN datatype.[1] Boolean data may only be TRUE, FALSE, or NULL. A Boolean is a "logical" datatype.
[1] The Boolean is named after George Boole, who lived in the first half of the 19th century and is considered the father of symbolic logic. One therefore capitalizes "Boolean," whereas the other datatypes get no respect.
The Oracle RDBMS does not support a Boolean datatype. You can create a table with a column of datatype CHAR(1) and store either "Y" or "N" in that column to indicate TRUE or FALSE. That is a poor substitute, however, for a datatype that stores actual 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.
Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be 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. You can replace a complicated Boolean expression involving many different variables and tests with a single Boolean variable that directly expresses the intention and meaning of the text.
Here is an example of an IF statement with a single Boolean variable (or function—you really can't tell the difference just by looking at this line of code):
IF report_requestedTHEN print_report (report_id);END IF;The beauty of this technique is that it not only makes your code a bit more self-documenting, it also has the potential to insulate your code from future change. For example, consider the human interface that needs to precede the previous code fragment. How do we know that a report was requested? Perhaps we ask the user to answer a question with a Y or an N, or perhaps the user must place a check in a checkbox or select an option from a drop-down list. The point is that it doesn't matter. We can freely change the human interface of our code, and, as long as that interface properly sets the report_requested Boolean variable, the actual reporting functionality will continue to work correctly.
In the previous example, we used a single Boolean variable in our condition. If the variable report_requested evaluates to TRUE, then the report prints. Otherwise, the print step is skipped. We could code that same IF statement as follows:
While the code in this example is logically equivalent to the "IF report_requested" formulation, it is superfluous and works against the nature of a Boolean variable. A Boolean variable itself evaluates to TRUE, FALSE, or NULL; you don't have to test the variable against those values. If you name your Boolean variables properly, you will be able to easily read the logic and intent of your IF-THEN logic by leaving out the unnecessary parts of the statement.
The fact that Boolean variables can be NULL has implications for IF...THEN...ELSE statements. For example, look at the difference in behavior between the following two statements:
IF report_requested THEN --Executes if report_requested = TRUEELSE --Executes if report_requested = FALSE or IS NULLEND IF; IF NOT report_requested THEN --Executes if report_requested = FALSEELSE --Executes if report_requeste = TRUE or IS NULLEND IF;If you need separate logic for each of the three possible cases, you can write a three-pronged IF statement as follows:
IF report_requestedTHEN --Executes if report_requested = TRUEELSIF NOT report_requested --Executes if report_requested = FALSEELSE --Executes if report_requested IS NULLEND IF;For more details on the effects of NULLs in IF statements, refer back to Chapter 4.
