Learning Oracle PL/SQL

2.9. Some Advanced Fundamentals

Okay, you've been introduced to block structure, variables, common operators, conditional statements, and iterative statements. As if that weren't enough fundamentals, there are a few more necessary details we want to expose you to:

• The challenge of null values in SQL and PL/SQL

• Naming rules for identifiers

• Scope of variables

• User-defined datatypes

• Interpreted versus compiled code

If, however, you are a beginner who is struggling with too many new concepts, you probably want to skip this section for now, and come back to it after you've worked more with actual programs.

2.9.1. NULLs in SQL and PL/SQL

Anyone new to the world of Oracle is likely to have a hard time making heads and tails of NULL .

When a variable, column, or constant has a value of NULL, its value is either undefined or unknown—that is, indeterminate. "Unknown" is 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.

Earlier in this chapter we saw how testing for null with the equality (=) operator (as opposed to the proper way, using IS NULL) puts you on the road to doom. Let's look at a few other close encounters you're likely to have with NULLs.

2.9.1.1. Null strings

If a string is null, you can't really compare it with anything, as we explained in the earlier discussion of operators. However, you can still combine a null string with non-null strings and get ...