Wouldn't it be nice if everything was knowable, and known? Hmmm. Maybe not. The question, however, is moot. We don't know the answer to many questions. We are surrounded by the Big Unknown, and because Oracle Corporation prides itself on providing database technology to reflect the real world, it supports the concept of a null value.
When a variable, column, or constant has a value of NULL, its value is unknown -- indeterminate. "Unknown" is very different from a blank or a zero or the Boolean value FALSE. "Unknown" means that the variable has no value at all and so cannot be compared directly with other variables. The following three rules hold for null values:
A null is never equal to anything else. None of the following IF statements can ever evaluate to TRUE:
my_string := ' ';
IF my_string = NULL THEN ...--This will never be true.
max_salary := 0;
IF max_salary = NULL THEN ...--This will never be true.
IF NULL = NULL THEN ...--Even this will never be true.
A null is never not equal to anything else.
Remember: with null values, you just never know. None of the following IF statements can ever evaluate to TRUE.
my_string := 'Having Fun';
your_string := NULL;
IF my_string != your_string THEN ..--This will never be true.
max_salary := 1234;
IF max_salary != NULL THEN ...--This will never be true.
IF NULL != NULL THEN ...--This will never be true.
When you apply a function to a null value, you generally receive a null value as a result
(there are some exceptions, listed below). A null value cannot be found in a string with the INSTR function. A null string has a null length, not a zero length. A null raised to the 10th power is still null.
my_string := NULL;
IF LENGTH (my_string) = 0 THEN ...--This will not work.
new_value := POWER (NULL, 10);--new_value is set to null value.
In general, whenever you perform a comparison involving one or more null values, the result of that comparison is also a null value -- which is different from TRUE or FALSE -- so the comparison cannot help but fail.
Whenever PL/SQL executes a program, it initializes all locally declared variables to null (you can override this value with your own default value). Always make sure that your variable has been assigned a value before you use it in an operation.
You can also use special syntax provided by Oracle to check dependably for null values, and even assign a null value to a variable. PL/SQL provides a special reserved word, NULL, to represent a null value in PL/SQL. So if you want to actually set a variable to the null value, you simply perform the following assignment:
my_string := NULL;
If you want to incorporate the possibility of null values in comparison operations, you must perform special case checking with the IS NULL and IS NOT NULL operators. The syntax for these two operators is as follows:
<identifier> IS NULL
<identifier> IS NOT NULL
where <identifier> is the name of a variable, a constant, or a database column. The IS NULL operator returns TRUE when the value of the identifier is the null value; otherwise, it returns FALSE. The IS NOT NULL operator returns TRUE when the value of the identifier is not a null value; otherwise, it returns FALSE.
Here are some examples describing how to use operators to check for null values in your program:
In the following example, the validation rule for the hire_date is that it cannot be later than the current date and it must be entered. If the user does not enter a hire_date, then the comparison to SYSDATE will fail because a null is never greater than or equal to (
) anything. The second part of the OR operator, however, explicitly checks for a null hire_date. If either condition is TRUE, then we have a problem.
IF hire_date >= SYSDATE OR hire_date IS NULL
DBMS_OUTPUT.PUT_LINE (' Date required and cannot be in future.');
In the following example, a bonus generator rewards the hard-working support people (not the salespeople). If the employee's commission is over the target compensation plan target, then send a thank you note. If the commission is under target, tell them to work harder, darn it! But if the person has no commission at all (that is, if the commission IS NULL), give them a bonus recognizing that everything they do aids in the sales effort. (You can probably figure out what my job at Oracle Corporation was.) If the commission is a null value, then neither of the first two expressions will evaluate to TRUE:
IF :employee.commission >= comp_plan.target_commission
ELSIF :employee.commission < comp_plan.target_commission
ELSIF :employee.commission IS NULL
PL/SQL treats a string of zero-length as a NULL. A zero-length string is two single quotes without any characters in between. The following two assignments are equivalent:
my_string := NULL;
my_string := '';
While it is generally true that functions which take a NULL argument return the null value, there are several exceptions:
Concatenation. There are two ways to concatenate strings: the CONCAT function (described in
) and the concatenation operator (double vertical bars:
). In both cases, concatenation ignores null values, and simply concatenates "around" the null. Consider the following examples:
CONCAT ('junk', NULL) ==> junk
'junk' || NULL || ' ' || NULL || 'mail' ==> junk mail
Of course, if all the individual strings in a concatenation are NULL, then the result is also NULL.
The NVL function. The NVL function (described in
) exists specifically to translate a null value to a non-null value. It takes two arguments. If the first argument is NULL, then the second argument is returned. In the following example, I return the string `Not Applicable' if the incoming string is NULL:
new_description := NVL (old_description, 'Not Applicable');
The REPLACE function. The REPLACE function (described in
) returns a string in which all occurrences of a specified match string are replaced with a replacement string. If the match_string is NULL, then REPLACE does not try to match and replace any characters in the original string. If the replace_string is NULL, then REPLACE removes from the original string any characters found in match_string.
Although there are some exceptions to the rules for null values, nulls must generally be handled differently from other data. If your data has NULLS, whether from the database or in local variables, you will need to add code to either convert your null values to known values, or use the IS NULL and IS NOT NULL operators for special case null value handling.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.