4.3 NULLS in PLSQL

Chapter 4
Variables and Program Data

4.3 NULLs in PL/SQL

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.
```

4.3.1 NULL Values in Comparisons

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.

4.3.2 Checking for NULL Values

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 THEN DBMS_OUTPUT.PUT_LINE (' Date required and cannot be in future.'); END IF;
```
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 THEN just_send_THANK_YOU_note (:employee_id); ELSIF :employee.commission < comp_plan.target_commission THEN send_WORK_HARDER_singing_telegram (:employee_id); ELSIF :employee.commission IS NULL THEN non_sales_BONUS (:employee_id); END IF;
```
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 := '';
```

4.3.3 Function Results with NULL Arguments

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 Chapter 11) 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 Chapter 13) 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 Chapter 11) 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.