Writing PL/SQL Expressions 77
3
AND
The
AND
operator is used to join two comparison expressions when you are interested in
testing whether both expressions are true. It can also be used for the same purpose with
two Boolean variables—to check to see whether both are equal to
true
.
The Syntax for the
AND
Operator
boolean_expression AND boolean_expression
In this syntax,
boolean_expression
can be any expression resulting in a Boolean, or
true
/
false
,value. This is often a comparison expression such as
(a = b)
,but can also
be a variable of the
BOOLEAN
datatype.
The
AND
operator returns a value of
true
if both expressions each evaluate to

OR
Operator
boolean_expression OR boolean_expression
In this syntax,
boolean_expression
can be any expression resulting in a Boolean, or
true
/
false
,value. This is often a comparison expression such as
(a = b)
,but can also
be a variable of the
BOOLEAN
datatype.
The
OR
operator returns a value of
true
if any one of the expressions evaluates to
true
.
A value of
false
is returned only if both the expressions evaluate to
false
. Table 3.8
shows some sample expressions using the
OR
operator.

string_2
are both character strings and can be either string
constants, string variables, or a string expression. The concatenation operator returns a
result consisting of all the characters in
string_1
followed by all the characters in
string_2
.
Listing 3.6 shows several ways in which you can use the concatenation operator.
L
ISTING
3.6
Use of the Concatenation Operator
1: --Demonstrate the concatenation operator
2: DECLARE
3: a VARCHAR2(30);
4: b VARCHAR2(30);
5: c VARCHAR2(30);
6: BEGIN
7: --Concatenate several string constants.
8: c := ‘Jack’ || ‘ AND ‘ || ‘Jill’;
9: DBMS_OUTPUT.PUT_LINE(c);
10: --Concatenate both string variables and constants.
11: a := ‘went up’;
12: b := ‘the hill’;
13: DBMS_OUTPUT.PUT_LINE(a || ‘ ‘ || b || ‘,’);
14: --Concatenate two string variables.
15: a := ‘to fetch a ‘;
16: b := ‘pail of water.’;
17: c := a || b;

• Datatype
• Case (upper versus lower)
The Effect of Character Set on String Comparisons
When comparing two strings to see if one is greater or less than another, the result
depends on the sort order of the underlying character set being used. In the typical ASCII
environment, all lowercase letters are greater than all uppercase letters, digits are less
than all letters, and the other characters fall in various places depending on their corre-
sponding ASCII codes. However, if you were working in an EBCDIC environment, you
would find that all the digits were greater than the letters and all lowercase letters are
less than all uppercase letters, so be careful.
The Datatype’s Effect on String Comparisons
The underlying datatype has an effect when comparing two string variables, or
when comparing a string variable with a constant. Remember that variables of
the
CHAR
datatype are fixed-length and padded with spaces. Variables of the
VARCHAR2
datatype are variable-length and are not automatically padded with spaces. When com-
paring two
CHAR
datatypes, Oracle uses blank-padded comparison semantics. This means
that Oracle conceptually adds enough trailing spaces to the shorter string to make it
equal in length to the longer string and then does the comparison. Trailing spaces alone
will not result in any differences being found between two springs. Oracle also does the
same thing when comparing two string constants. However, when one of the values in a
O
UTPUT
A
NALYSIS
N

18: IF fixed_length_20 = fixed_length_10 THEN
19: DBMS_OUTPUT.PUT_LINE(‘Char: ‘’’ || fixed_length_10 || ‘’’ = ‘’’
20: || fixed_length_20 || ‘’’’);
21: END IF;
22: --Comparison of a fixed length string and a literal also
23: --results is the use of blank-padded comparison semantics.
24: IF fixed_length_10 = ‘Donna’ THEN
25: DBMS_OUTPUT.PUT_LINE(‘Char and constant: ‘’’
26: || fixed_length_10 || ‘’’ = ‘’’ || ‘Donna’ || ‘’’’);
27: END IF;
28: --But compare a variable length string
29: --against a fixed length, and the
30: --trailing spaces do matter.
31: var_length_10 := ‘Donna’;
32: IF fixed_length_10 = var_length_10 THEN
33: DBMS_OUTPUT.PUT_LINE(‘Char and Varchar2: ‘’’
34: || fixed_length_10 || ‘’’ = ‘’’
35: || var_length_10 || ‘’’’);
36: ELSE
37: DBMS_OUTPUT.PUT_LINE(‘Char and Varchar2: ‘’’
38: || fixed_length_10 || ‘’’ NOT = ‘’’
39: || var_length_10 || ‘’’’);
40: END IF;
41: --The maximum lengths of varchar2 strings do not matter,
42: --only the assigned values.
80 Day 3
I
NPUT
05 7982 ch03 11.8.00 11:22 AM Page 80
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

strings are not considered to be equal.
The Effect of Case on String Comparisons
PL/SQL string comparisons are always case-sensitive. The obvious ramification of this is
that a lowercase string such as
‘aaa’
is not considered equal to its uppercase equivalent
of
‘AAA’
. But case also makes a difference when comparing two strings to see which is
greater. In an ASCII environment, the letter
‘A’
will be less than the letter
‘B’
. However,
the letter
‘a’
will not only be greater than
‘B’
; it will be greater than
‘Z’
.
O
UTPUT
A
NALYSIS
If you need to perform case-insensitive string comparisons, use PL/SQL’s
built-in
UPPER()
function. For example:
IF UPPER(‘a’) < UPPER(‘B’) THEN...

14: ELSE
15: DBMS_OUTPUT.PUT_LINE(‘Payment can wait a while.’);
16: END IF;
17: --In reality, the time does not matter when speaking of a due date.
18: IF TRUNC(payment_due_date) = TRUNC(SYSDATE) THEN
19: DBMS_OUTPUT.PUT_LINE(‘Wrong! Payment is due today!’);
20: ELSE
21: DBMS_OUTPUT.PUT_LINE(‘Wrong! Payment can wait a while.’);
22: END IF;
23: END;
24: /
Today is 01-Jun-1997
Payment is due on 01-Jun-1997
Payment can wait a while.
Wrong! Payment is due today!
PL/SQL procedure successfully completed.
Today’s date and the payment due date both match, yet the
IF
statement in line
12 failed to detect this. Why? Because
SYSDATE
is a function that returns the cur-
rent date and time, with the time resolved down to the second. The
payment_due_date
variable will contain a time of midnight because the
TRUNC
function was used when the
82 Day 3
I
NPUT

Understanding the effects of a null value on an expression is particularly important,
especially when you move into retrieving data from a database. The remainder of this
chapter discusses each of these items in detail.
Expressions Defined
Simply put, an expression is some combination of variables, operators, literals, and func-
tions that returns a single value. Operators are the glue that hold an expression together
and are almost always present. The other elements might not all be present in every
expression.
In its very simplest form, an expression might simply consist of a literal value, a variable
name, or a function call. The first few entries in Table 3.9 are examples of this type of
expression. More typical expressions involve two values and an operator, with the opera-
tor defining the action to be taken and the result to be returned. Complex expressions
05 7982 ch03 11.8.00 11:22 AM Page 83
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
can be built up by stringing several simple expressions together with various operators
and function calls. Finally, the unary operators can be applied to any expression or value.
T
ABLE
3.9
Sample Expressions
Expression Comments
1000
Evaluates to one thousand
some_variable_name
Evaluates to the contents of the variable
SYSDATE
An Oracle function that returns the current date
1000 + 2000
A typical expression using a binary operator
-1000

**
,
NOT
Exponentiation and logical negation
Second
+
,
-
Arithmetic identity and negation (
+
and
-
used as unary operators)
Third
*
,
/
Multiplication and division
Fourth
+
,
-
,
||
Addition, subtraction, and string concate-
nation
Fifth
=
,
<>

2.
1-25<=10*4-20
3.
1-25<=40-20
4.
-24<=20
5.
true
You can control the order in which Oracle evaluates an expression by using parentheses.
Oracle will evaluate any part of an expression in parentheses first. If parentheses are
nested, Oracle will always evaluate the innermost expression first and then move out-
wards. Here is what happens to the preceding expression if you add some parentheses:
1.
(1-5)**2<=10*(4-20)
2.
(-4)**2<=10*(-16)
3.
16<=-160
4.
false
05 7982 ch03 11.8.00 11:22 AM Page 85
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Types of Expressions
One way of classifying expressions is by the datatype of the resulting value. Using this
scheme, expressions can be classified as one of these types:
•Arithmetic or numeric
• Boolean
•String
• Date
Any expression returning a numeric value is referred to as an arithmetic expression,or

without first assigning a value. Because the variable has no assigned value, the result of
the expression can’t be known. More commonly, nulls are encountered when reading
data from a database. Oracle, like any other relational database, does not force you to
86 Day 3
Use parentheses in complex expressions, even when they are not strictly nec-
essary, in order to make the intended order of evaluation clear to other pro-
grammers.
Tip
05 7982 ch03 11.8.00 11:22 AM Page 86
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing PL/SQL Expressions 87
3
store a value for each column in a table. When no specific value is stored, the contents of
that column are considered unknown, and the column is referred to as being
null
.
The effects of nulls are particularly insidious when writing Boolean expressions,
such as the
WHERE
clause in a SQL
SELECT
statement. SQL uses what is called
three-valued logic. Three-valued logic says that the result of a Boolean expression can be
either
true
,
false
,or
NULL
. Many a programmer has felt the sting of an

22: --expect to always be true.
23: IF (a = n) OR (a <> n) THEN
24: DBMS_OUTPUT.PUT_LINE(‘(a = n) or (a <> n) is true’);
25: ELSE
26: DBMS_OUTPUT.PUT_LINE(‘(a = n) or (a <> n) is not true’);
27: END IF;
28: --TRUE and NULL = NULL
29: IF (a = 2) AND (a <> n) THEN
30: DBMS_OUTPUT.PUT_LINE(‘TRUE and NULL = TRUE’);
31: ELSE
32: DBMS_OUTPUT.PUT_LINE(‘TRUE and NULL = NULL’);
33: END IF;
N
EW
T
ERM
I
NPUT
continues
05 7982 ch03 11.8.00 11:22 AM Page 87
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
34: --TRUE or NULL = TRUE
35: IF (a = 2) OR (a <> n) THEN
36: DBMS_OUTPUT.PUT_LINE(‘TRUE or NULL = TRUE’);
37: ELSE
38: DBMS_OUTPUT.PUT_LINE(‘TRUE or NULL = NULL’);
39: END IF;
40: --NOT NULL = NULL
41: IF (NOT (a = n)) IS NULL THEN
42: DBMS_OUTPUT.PUT_LINE(‘NOT NULL = NULL’);

Listing 3.9 is a somewhat contrived example, but it illustrates very well the
effects of nulls on comparison expressions. Take a close look at what is going on
here. The first
IF
statement in line 10 tests for
a = n
. As you might expect, this is not
true
,but it is important to understand that it is not
false
either. The second
IF
statement
in line 16 proves this. The test there is for
a <> n
,the exact opposite of the previous
88 Day 3
L
ISTING
3.9
continued
O
UTPUT
A
NALYSIS
05 7982 ch03 11.8.00 11:22 AM Page 88
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing PL/SQL Expressions 89
3
comparison, and it also is not

ple) to be a null. A string of spaces, however, is not the same as an empty
string, and is not null.
Note
The logical operators
AND
,
OR
, and
NOT
are often used to link together comparison expres-
sions. Table 3.11 shows how these operators function in expressions with null values.
T
ABLE
3.11
Three-Valued Logic Truth Table
Operator Expression Result
AND TRUE AND TRUE TRUE
TRUE AND FALSE FALSE
TRUE AND NULL NULL
FALSE AND NULL FALSE
NULL AND NULL NULL
OR TRUE OR TRUE TRUE
TRUE OR FALSE TRUE
TRUE OR NULL TRUE
FALSE OR NULL FALSE
NULL OR NULL NULL
NOT NOT TRUE FALSE
NOT FALSE TRUE
NOT NULL NULL
05 7982 ch03 11.8.00 11:22 AM Page 89

TRUE
,
FALSE
, and
NULL
.
Tip
There are some exceptions to the general rule that nulls propagate in expressions. Null
character strings are sometimes handled as if they were zero-length strings, and PL/SQL
does have some functions and operators that have been specifically designed to help you
work with nulls.
You can concatenate strings, even if one is null, and get the results you would expect.
This is because the concatenation operator simply ignores any null strings. However, if
all the strings are null, the result will be null. Also bear in mind that PL/SQL treats a
zero-length
VARCHAR2
string as a null value.
Treating a zero-length string as a null value is an Oracle-specific behavior
that is not specified in the ANSI standard for SQL.
Note
You can use the
IS NULL
operator to see whether or not a particular variable or expres-
sion is null. It allows your code to detect and act on null values. You saw an example of
this earlier in Listing 3.5. The
IS NULL
operator returns only a
true
or
false

return the value of the second argument as its result. The end result is that the
value_if_expression_is_null
argument becomes an alternate value for
expression
,to
be used if
expression
is null.
Lines 44 through 52 of Listing 3.9 show an interesting use of the
NVL
function to account
for the possibility of the variable
n
being null. You can read more about
NVL
in
Appendix B.
The built-in SQL
DECODE
function actually treats null as a specific value instead of an
unknown value. It might seem contradictory, but it’s useful.
DECODE
is also described in
Appendix B.
Always use the IS NULL operator when checking for null values. Do not use
the equality or inequality operators to compare a variable to null. You can
code a statement such as IF some_var = NULL, but you won’t get the
results you might expect. Use IF some_var IS NULL instead.
Caution
,

when it makes sense. The second and more preferred method is to code your conversions
explicitly.
Implicit Conversion
When you mix different datatypes in an expression, Oracle will convert them for
you when it makes sense to do so. This is referred to as implicit conversion.
Listing 3.10 shows several examples of implicit conversion.
L
ISTING
3.10
Implicit Conversion Examples
1: --Demonstrate implicit conversion
2: DECLARE
3: d1 DATE;
4: cd1 VARCHAR2(10);
5: cd2 VARCHAR2(10);
6: n1 NUMBER;
7: cn1 VARCHAR2(10);
8: cn2 VARCHAR2(10);
9: BEGIN
10: --Assign a value to this string which represents a date.
11: cd1 := ‘15-Nov-61’;
12: --Now assign the string to a date variable.
13: --The conversion is implicit.
14: d1 := cd1;
15: --Now assign that date variable to another string.
16: --Again the conversion
17: --is implicit, but this time the conversion is
18: --from a date to a string.
19: cd2 := d1;
20: --Display the two character strings to show that they are the same.

internal format before it can assign the value to
d1
. In line 19, that date is again convert-
ed back to a string format. Lines 23 through 28 repeat the same process, but this time
with a number.
Implicit conversions are convenient, but can sometimes cause problems. In relying on
them, you are relying on Oracle’s built-in assumptions and on default settings you might
not even be aware of, and which might change from one release to another. The format
of a date leads to some good examples. Did you know that Oracle’s default date format
varies depending on the language setting? That it can also be installation-dependent? And
that it can vary between a client PC executing a Developer/2000 script and a database
server? In fact, the date format can even be changed for the duration of a particular ses-
sion. Figure 3.2 illustrates this by showing the same PL/SQL code succeeding once and
then failing after the date format has been changed.
O
UTPUT
A
NALYSIS
F
IGURE
3.2
The default date for-
mat is changed.
05 7982 ch03 11.8.00 11:22 AM Page 93
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
For the reasons just listed, it is often safer to code conversions explicitly. Explicit conver-
sions also better document your code by making it clear to other programmers exactly
what is happening.
Explicit Conversion
Oracle has several built-in functions that are designed to convert information from one

11: --Here are some common date formats which you might encounter.
12: d1 := TO_DATE(‘1/1/02’,’mm/dd/yy’);
13: d2 := TO_DATE(‘1-1-1998’,’mm-dd-yyyy’);
14: d3 := TO_DATE(‘Jan 1, 2000’,’mon dd, yyyy’);
15: --Year 2000 problems? Note the effect of using rr instead of yy.
16: d4 := TO_DATE(‘1/1/02’,’mm/dd/rr’);
17: DBMS_OUTPUT.PUT_LINE(‘d1 = ‘ || TO_CHAR(d1,’dd-Mon-yyyy’));
18: DBMS_OUTPUT.PUT_LINE(‘d2 = ‘ || TO_CHAR(d2,’mm/dd/yyyy’));
19: DBMS_OUTPUT.PUT_LINE(‘d3 = ‘ || TO_CHAR(d3,’Day, Month dd, yyyy’));
20: DBMS_OUTPUT.PUT_LINE(‘d4 = ‘ || TO_CHAR(d4,’Dy, Mon dd, yyyy’));
21: --Here are some examples of numeric conversions.
22: n1 := TO_NUMBER (‘123.99’,’999D99’);
94 Day 3
I
NPUT
05 7982 ch03 11.8.00 11:22 AM Page 94
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing PL/SQL Expressions 95
3
23: n2 := TO_NUMBER (‘$1,235.95’,’$9G999D99’);
24: DBMS_OUTPUT.PUT_LINE(‘n1 = ‘ || TO_CHAR(n1,’999D99’));
25: DBMS_OUTPUT.PUT_LINE(‘n2 = ‘ || TO_CHAR(n2,’$9G999D99’));
26: END;
27: /
d1 = 01-Jan-1902
d2 = 01/01/1998
d3 = Saturday , January 01, 2000
d4 = Tue, Jan 01, 2002
n1 = 123.99
n2 = $1,235.95

AND
operator requires that both its operands
be true in order to return a
true
result. If one of the operands is unknown, you
can’t be sure that if it were known it would be
true
,so
AND
must evaluate to
false
in this case. Things are different, however, for the expression
TRUE OR NULL
. The
OR
operator only requires one of its operands to be
true
in order to return a
true
result. In the case of
TRUE OR NULL
, you do know that one operand is
true
.
O
UTPUT
A
NALYSIS
05 7982 ch03 11.8.00 11:22 AM Page 95
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

of the operands is null, and it can never be known if two values are equal when one
of the values is unknown. The second expression uses the
IS NULL
operator, which
is designed to check for nulls. It specifically checks to see if the value of X is
unknown and evaluates to
true
if that is the case.
Q When I am comparing strings, especially when comparing a
CHAR
string to a
VARCHAR2
string, is there a convenient way to tell PL/SQL to ignore any trail-
ing spaces in the
CHAR
string?
A Yes. Use the built-in
RTRIM
function. For example:
IF RTRIM(char_string) =
varchar2_string then...
Q I’m comparing two dates and only want to know if they are in the same year.
Can I use the
TRUNC
function to accomplish this?
A Yes. By default, the
TRUNC
function truncates the time portion of a date, but the
optional second argument enables you to specify a different point of truncation. To
compare only the years, you can write: