PL/SQL lets you compare variables and constants in both SQL and procedural
PL/SQL Expressions and Comparisons
expressions separated by relational operators. Often, Boolean expressions are connected by the logical operatorsAND,OR, andNOT. A Boolean expression always yieldsTRUE,FALSE, orNULL.
In a SQL statement, Boolean expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, Boolean expressions are the basis for conditional control. There are three kinds of Boolean expressions: arithmetic, character, and date.
Boolean Arithmetic Expressions
You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments
number1 := 75; number2 := 70;
the following expression is true: number1 > number2
Boolean Character Expressions
You can compare character values for equality or inequality. By default, comparisons are based on the binary values of each byte in the string.
For example, given the assignments string1 := 'Kathy';
string2 := 'Kathleen';
the following expression is true: string1 > string2
By setting the initialization parameterNLS_COMP=ANSI, you can make comparisons use the collating sequence identified by theNLS_SORT initialization parameter. A
collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.
Depending on the value of theNLS_SORT parameter, you can perform comparisons that are case-insensitive and even accent-insensitive. A case-insensitive comparison still returns true if the letters of the operands are different in terms of uppercase and lowercase. An accent-insensitive comparison is case-insensitive, and also returns true if the operands differ in accents or punctuation characters. For example, the character values'True'and'TRUE'are considered identical by a case-insensitive comparison; the character values'Cooperate','Co-Operate', and'coöperate' are all considered the same. To make comparisons case-insensitive, add_CI to the end of your usual value for theNLS_SORT parameter. To make comparisons
accent-insensitive, add_AI to the end of theNLS_SORT value.
There are semantic differences between theCHAR andVARCHAR2 base types that come into play when you compare character values. For more information, seeAppendix B. Many types can be converted to character types. For example, you can compare, assign, and do other character operations usingCLOB variables. For details on the possible conversions, see"PL/SQL Character and String Types" on page 3-4.
PL/SQL Expressions and Comparisons
Boolean Date Expressions
You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments
date1 := '01-JAN-91'; date2 := '31-DEC-90';
the following expression is true: date1 > date2
Guidelines for PL/SQL Boolean Expressions
■ In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. For example, the followingIF condition might not yieldTRUE:
DECLARE
fraction BINARY_FLOAT := 1/3; BEGIN
IF fraction = 11/33 THEN
dbms_output.put_line('Fractions are equal (luckily!)'); END IF;
END; /
■ It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because100 < tax yields a Boolean value, which cannot be compared with the number 500:
100 < tax < 500 -- not allowed The debugged version follows: (100 < tax) AND (tax < 500)
■ A Boolean variable is itself either true or false. You can just use the variable in a conditional test, rather than comparing it to the literal valuesTRUE andFALSE. For example, the following loops are all equivalent:
DECLARE
done BOOLEAN ; BEGIN
-- Each WHILE loop is equivalent done := FALSE;
WHILE done = FALSE LOOP
done := TRUE; END LOOP; done := FALSE;
WHILE NOT (done = TRUE) LOOP
done := TRUE; END LOOP; done := FALSE; WHILE NOT done LOOP
done := TRUE; END LOOP; END;
PL/SQL Expressions and Comparisons
/
■ UsingCLOB values with comparison operators, or functions such asLIKE and BETWEEN, can create temporary LOBs. You might need to make sure your temporary tablespace is large enough to handle these temporary LOBs.
CASE Expressions
ACASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a procedure call.
TheCASE expression uses aselector, an expression whose value determines which alternative to return. ACASE expression has the following form:
CASE selector
WHEN expression1 THEN result1 WHEN expression2 THEN result2 ...
WHEN expressionN THEN resultN [ELSE resultN+1]
END
The selector is followed by one or moreWHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The firstWHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. For example:
DECLARE grade CHAR(1) := 'B'; appraisal VARCHAR2(20); BEGIN appraisal := CASE grade
WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' WHEN 'D' THEN 'Fair' WHEN 'F' THEN 'Poor' ELSE 'No such grade' END;
dbms_output.put_line('Grade ' || grade || ' is ' || appraisal); END;
/
The optionalELSE clause works similarly to theELSE clause in anIF statement. If the value of the selector is not one of the choices covered by aWHEN clause, theELSE clause is executed. If noELSE clause is provided and none of theWHEN clauses are matched, the expression returnsNULL.
An alternative to theCASE expression is theCASE statement, where eachWHEN clause can be an entire PL/SQL block. For details, see"Using the CASE Statement" on page 4-3.