Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

PL/SQL supports a full set of logical operators that may be used for logical and arithmetic expressions. The operations defined on DATE type allow manipulation and comparison of DATE type variables . Use "IS NULL" and "NOT NULL" in comparison statements if any of the values may be NULL. The following is a summary of comparison operators.

= is equal to IF (a = b) THEN != is not equal to IF (a != b) THEN <> is not equal to IF (a <> b) THEN > is greater than IF (a = b) THEN >= is greater than or equal to IF (a != b) THEN < is less than IF (a <> b) THEN <= is less than or equal to IF (a != b) THEN

The following function returns TRUE if the DATE parameter passed is yesterday .

FUNCTION is_yesterday (v_date in DATE) RETURN BOOLEAN IS BEGIN RETURN (TRUNC(v_date) = TRUNC(SYSDATE-1)); END is_yesterday;

This function can be used in conjunction with operators.

IF (a < b) AND is_yesterday(date_variable) THEN

You can also use the following for comparisons.

• BETWEEN

• LIKE

  % For string substitution

  _ For a single character substitution

• IN

To test if a student course name contains the string "MATH," use string substitution on either side. The following returns TRUE if MATH is anywhere in the variable.

IF (variable_name LIKE '%MATH%') THEN

What if the variable name is set to "Math and Science." The test fails because the string "MATH" is not the same as "Math." When not sure about case, convert the variable to upper or lower case and then do the comparison.

IF (UPPER(variable_name) LIKE '%MATH%') THEN

The following returns TRUE when the uppercase variable has an "E." Any single character follows that "E." And a "B" follows that single character.

IF (UPPER(variable_name) LIKE '%E_B%') THEN

When variable_name = EEB this test is true. When variable_name = abcexbx this test is true.

Comparisons do include endpoints. The following is TRUE if VARIABLE equals MIN_VALUE of MAX_VALUE.

IF (variable BETWEEN min_value AND max_value) THEN

The IN operator returns TRUE if a variable is found in a set.

IF (variable IN ('BOSTON', 'CHICAGO','LONDON')) THEN

The IN parameters can be variables. For example:

CREATE OR REPLACE procedure test(arg VARCHAR2) IS chicago VARCHAR2(10) := 'CHICAGO'; new_york VARCHAR2(10) := 'NEW_YORK'; BEGIN IF (arg IN (chicago, new_york)) THEN Other code END;

Use SQL functions to reduce unnecessary comparison logic in PL/SQL. This applies to comparing different column values from a single row. Suppose you select two columns and ultimately want the larger of the two. One approach is to code the following:

SELECT COL_1, COL_2 INTO VAR_1, VAR_2 FROM etc. IF VAR_1 > VAR_2 THEN etc.

The SQL functions GREATEST, LEAST can assist.

SELECT COL_1, COL_2, GREATEST(COL_1, COL_2) INTO VAR_1, VAR_2, VAR_3 etc.

Refer to Section 11.14, "Miscellaneous String Functions," for a description of GREATEST and LEAST ”they also operate on strings.