11.5 Logical Operators

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.