This is an excerpt from the Rampant TechPress book Easy Oracle PL/SQL Programming: Get Started Fast with Working PL/SQL Code Examples by John Garmany (ISBN: 0-9759135-7-3).
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 simple concept, but
NULL math is a very common PL/SQL bug.
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.
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...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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.
A comparison results in a true or a false. Here are some
comparisons using the variables above.
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.
The variable v_line will always be 'always end up here' because
n_x < 10 will always be false.
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.
