Listing 4.7. continued

46:      || var_length_20 || `'' = `''
47:      || var_length_10 || `''');
48:   ELSE
49:     DBMS_OUTPUT.PUT_LINE(`Both Varchar2: `''
50:       || var_length_20 || `'' NOT = `''
51:       || var_length_10 || `''');
52:   END IF;
53: END;
54: /

OUTPUT

Constant: `jonathan' = `Jonathan          `
Char: `Donna     ` = `Donna               `
Char and constant: `Donna     ` = `Donna'
Char and Varchar2: `Donna     ` NOT = `Donna'
Both Varchar2: `Donna' = `Donna'
PL/SQL procedure successfully completed.

ANALYSIS
You can see from the output that the first three comparisons in Listing 4.7 use blank-padded comparison semantics. The strings being compared are considered to be equal even though the number of trailing spaces differs in each case. The fourth comparison however, compares a VARCHAR2 variable against a CHAR variable. Because one of the strings in question is variable length, the trailing spaces count, and the two 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'.

Use of Comparison Operators with Dates

Date comparison works pretty much as you might expect and has fewer complexities than string comparisons do. Earlier dates are considered to be "less than" later dates, and it follows that more recent dates are "greater than" earlier dates. The only complication arises from the

fact that PL/SQL date variables also contain a time component. Listing 4.8 illustrates this and one potential problem to be aware of when comparing date values against each other.

INPUT
Listing 4.8. Date comparison example.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   payment_due_date  DATE;
 4: BEGIN
 5:   --In real life the payment_due date might be read from
 6:   --a database or calculated based on information from a database.
 7:   payment_due_date := TO_DATE(`1-Jun-1997','dd-mon-yyyy');
 8:   --Display the current date and the payment date.
 9:   DBMS_OUTPUT.PUT_LINE(`Today is ` || TO_CHAR(SYSDATE,'dd-Mon-yyyy'));
10:   DBMS_OUTPUT.PUT_LINE(`Payment is due on `
11:   || TO_CHAR(payment_due_date,'dd-Mon-yyyy'));
12:   IF payment_due_date = SYSDATE THEN
13:     DBMS_OUTPUT.PUT_LINE(`Payment is due today.');
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: /

OUTPUT

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.

ANALYSIS
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 current date and time, with the time resolved down to the second. The payment_due_date variable will contain a time of midnight because none was specified in the assignment statement in line 7. So the only time line 12 would function correctly would be for one second at midnight at the beginning of 1-Jun-1997. In line 18, the TRUNC function is used to truncate the time values from the two dates, resulting in a comparison that works as desired in this case.

Having the time as part of a date variable is not necessarily a bad thing. It's just something you need to be aware of, especially when comparing dates with each other.

Expressions

When you combine values and operators to produce a result, you have an expression. You have already learned about the various datatypes available in PL/SQL, and you have just read about PL/SQL's extensive collection of operators. In addition, in order to use expressions effectively in your code, you also need to understand

• Operator precedence
• Use of parentheses
• Types of expressions
• The effects of null values in an expression
• Conversion between datatypes

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 functions 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 4.9 are examples of this type of expression. More typical expressions involve two values and an operator, the operator defining the action to be taken and the result to be returned. Complex expressions 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.

Table 4.9. Sample expressions.