Assignments, Initializations and NULLs

We have already seen that the assignment operator is the colon equal (:=) syntax and we have seen how this is used to assign values to a variable both within the code and upon declaration.  It is a good practice to initialize variables to a value when they are declared.  This is especially important with number variables to avoid null math errors.

Many programming languages avoid NULLs by requiring that a variable be initialized before use.  However, in a database, NULLs are commonly used and are not considered errors.  Anytime a row is inserted into a table with only some of the column values defined, NULLs are used for the undefined columns.  Thus NULLs are values in the database.

The value of NULL is ‘undefined’.  That means it is not zero, or one, or anything.  It is undefined.  Because the value of NULL is undefined it can be tricky to work with.

Null math is using any NULL value in a mathematical equation. Null math always returns a NULL.  This is a simply concept, but NULL math is a very common PL/SQL bug.

SQL> declare
  2    n_1 number;  -- null
  3    n_2 number := 0;
  4    n_3 number := 1;
  5  begin
  6    dbms_output.put_line ('Addition:       '|| n_1 + 5);
  7    dbms_output.put_line ('Subtraction:    '|| n_1 - 5);
  8    dbms_output.put_line ('Multiplication: '|| n_1 * 5);
  9    dbms_output.put_line ('Division:       '|| n_1 + 5);
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at line 6

The example above fails when dbms_output.out_line attempts to convert n_1 + 5 from a number to a character because it has the value of NULL.

In the example below, the value of n_2 is shown.  Notice that there is no value when n_1 is added because it is NULL .

SQL> declare
  2    n_1 number;  -- null
  3    n_2 number := 0;
  4    n_3 number := 1;
  5  begin
  6    n_2 := n_2 + n_3;
  7    dbms_output.put_line ('Results: '|| n_2);
  8    n_2 := n_1 + n_3;
  9    dbms_output.put_line ('Results: '|| n_2);
 10  end;
 11  / 

Results: 1
Results:

This is a very common code bug and it can be hard to locate.  So with PL/SQL , the developer must contend not only with division by zero, but also null math.

NULLs can also be problematic with comparisons.  Since NULL  is undefined the developer must insure that the correct comparison is used.  In the example below, n_1 is a null while n_2 is a number.

n_1 number;              -- null
n_2 number := 4;

A comparison results in a true or a false.  Here are some comparisons using the variables above.

(n_2 > 2)    true;  --4>2.
(n_1 > 2)    false; --null>2 undefined therefore false.
(n_1 < n_2)  false; --null < 4;
                             undefined therefore false.
(n_1 = n_2)  false; --null = 4;
                             undefined therefore false.
(n_1 <> n_2) true;  --null <> 4;
                           undefined is not equal to 4.

No matter how n_1 is used in the comparison, it is still an undefined comparison and therefore false, except if the “not equal” comparison is used.  The “not equal” comparison returns true because undefined can never be equal to a value.  This is important in the if/then and loop flow control discussed in chapter two.  If the developer selects the wrong comparison, it may return a “false” due to a variable not being initialized or return a “true” using the “not equal” comparison.  If the variable n_x has not been initialized then the following will always branch to the ELSE clause.

if (n_x < 10)
  then v_line := ‘Never get here’;
else v_line := ‘always end up here’;
end if; 

The variable v_line will always be ‘always end up here’ because n_x < 10 will always be false.

if (n_x <> 10)
  then v_line := ‘this is true’;
else v_line := ‘this is false’;
end if;

In the above example, v_line will always be ‘this is true’ because n_x <> 10 will always be true.  If this is the behavior desired then the code will work.  It will be very confusing to the code maintainer, who has to figure out what the initial coder was trying to accomplish.  You should use PL/SQL’s natural language structure to make code understandable and avoid “tricks” that accomplish a task but are confusing or difficult to understand.  If the code is confusing to read, it’s going to be hard to maintain.  Good PL/SQL developers get in the habit of using comments to explain what actions are being performed and why you are making calculations.  Next we will examine nested blocks and learn about the “scope” of PL/SQL variables.