To compare two values for equality, use the = operator. Its formula is:

Value1 = Value2

It can be illustrated as follows:

To find out if two values are not equal, use the <> operator. Its formula is:

Value1 <> Value2

It can be illustrated as follows:

The Not Equal operator <> is the opposite to the Equality operator =.

To find out whether one value is lower than another, use the < operator. Its formula is:

Value1 < Value2

It can be illustrated as follows:

To know if two values are the same or if the first is less than the second, you use the <= operator. Its formula is:

Value1 <= Value2

It can be illustrated as follows:

To find out if one value is strictly greater than another, you can use the > operator. Its formula is:

Value1 > Value2

It can be illustrated as follows:

The > operator is the opposite to <=.

The greater than and the equality operators can be combined to produce an operator as follows: >=. Its formula is:

Value1 >= Value2

It can be illustrated as follows:

The >= operator is the opposite to <.

A condition statement is an expression you formulate to evaluate it. The statement may appear as follows:

Keyword Expression
	Statement

To start, type the BEGIN keyword. Then, you must use the END keyword followed by a semi-colon. On the next line, type /. Therefore, the formula to follow is:

Keyword Expression
BEGIN
	Statement Line 1
	Statement Line 2
	
	Statement Line n
END;
/

The primary comparison you can perform on a statement is to find out whether it is true. This operation is performed using an IF statement. Its basic formula is:

IF Condition
	Statement

When creating an IF statement, first make sure you provide a Condition expression that can be evaluated to produce true or false. To create this Condition, you can use variables and the logical comparison operator reviewed above.

Here is an example:

SQL> DECLARE
  2     UnitPrice1 number(6, 2);
  3     UnitPrice2 number(6, 2);
  4     IsGreater varchar2(20);
  5  BEGIN
  6     UnitPrice1 := 12.50;
  7     UnitPrice2 := 10.55;
  8     IF UnitPrice1 > UnitPrice2 THEN
  9         IsGreater := 'True';
 10     END IF;
 11     DBMS_OUTPUT.PUT_LINE('Unit Price1: ' || UnitPrice1);
 12     DBMS_OUTPUT.PUT_LINE('Unit Price2: ' || UnitPrice2);
 13     DBMS_OUTPUT.PUT_LINE('Comparison:  ' || IsGreater);
 14  END;
 15  /
Unit Price1: 12.5
Unit Price2: 10.55
Comparison:  True

PL/SQL procedure successfully completed.

The IF condition we used above is appropriate when you only need to know if an expression is true. There is nothing to do in other alternatives. Consider the following code:

SQL> DECLARE
  2     UnitPrice1 number(6, 2);
  3     UnitPrice2 number(6, 2);
  4     IsGreater varchar2(20);
  5  BEGIN
  6     UnitPrice1 := 12.50;
  7     UnitPrice2 := 10.55;
  8     IF UnitPrice1 < UnitPrice2 THEN
  9         IsGreater := 'False';
 10     END IF;
 11     DBMS_OUTPUT.PUT_LINE('Unit Price1: ' || UnitPrice1);
 12     DBMS_OUTPUT.PUT_LINE('Unit Price2: ' || UnitPrice2);
 13     DBMS_OUTPUT.PUT_LINE('Comparison:  ' || IsGreater);
 14  END;
 15  /
Unit Price1: 12.5
Unit Price2: 10.55
Comparison:

PL/SQL procedure successfully completed.

Notice that, in case the expression to examine produces a false result, there is nothing to do. Sometimes this will happen. The solution is to add a second that uses the ELSE keyword. Here is an example:

SQL> DECLARE
  2     UnitPrice1 number(6, 2);
  3     UnitPrice2 number(6, 2);
  4     IsGreater varchar2(20);
  5  BEGIN
  6     UnitPrice1 := 12.50;
  7     UnitPrice2 := 10.55;
  8     IF UnitPrice1 > UnitPrice2 THEN
  9             IsGreater := 'True';
 10     ELSE
 11             IsGreater := 'False';
 12     END IF;
 13     DBMS_OUTPUT.PUT_LINE('Unit Price1: ' || UnitPrice1);
 14     DBMS_OUTPUT.PUT_LINE('Unit Price2: ' || UnitPrice2);
 15     DBMS_OUTPUT.PUT_LINE('Comparison:  ' || IsGreater);
 16  END;
 17  /
Unit Price1: 12.5
Unit Price2: 10.55
Comparison:  True

PL/SQL procedure successfully completed.

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
    WHEN Value1 THEN Result
    WHEN Value2 THEN Result

    WHEN Value_n THEN Result
END

Here is an example:

SQL> DECLARE
  2     CharGender char(1);
  3     Gender varchar2(20);
  4  BEGIN
  5     CharGender := 'F';
  6     CASE CharGender
  7             WHEN 'm' THEN Gender := 'Male';
  8             WHEN 'M' THEN Gender := 'Male';
  9             WHEN 'f' THEN Gender := 'Female';
 10             WHEN 'F' THEN Gender := 'Female';
 11     END CASE;
 12
 13     DBMS_OUTPUT.PUT_LINE('Gender: ' || Gender);
 14  END;
 15  /
Gender: Female

PL/SQL procedure successfully completed.

If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. The formula of the CASE statement is:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

SQL> DECLARE
  2     CharGender char(1);
  3     Gender varchar2(20);
  4  BEGIN
  5     CharGender := 'H';
  6     CASE CharGender
  7             WHEN 'm' THEN Gender := 'Male';
  8             WHEN 'M' THEN Gender := 'Male';
  9             WHEN 'f' THEN Gender := 'Female';
 10             WHEN 'F' THEN Gender := 'Female';
 11             ELSE Gender := 'Unknown';
 12     END CASE;
 13     DBMS_OUTPUT.PUT_LINE('Gender: ' || Gender);
 14  END;
 15  /
Gender: Unknown

PL/SQL procedure successfully completed.

If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL.

To examine a condition and evaluate it before taking action, you can use the WHILE operator. The basic formula of this statement is:

WHILE Expression LOOP
    Statement
END LOOP;

When implementing this statement, first provide a Boolean Expression after the WHILE keyword. The Expression must produce a true or a false result. If the Expression is true, then the interpreter executes the Statement. After executing the Statement, the Expression is checked again. AS LONG AS the Expression is true, it will keep executing the Statement. When or once the Expression becomes false, it stops executing the Statement. This scenario can be illustrated as follows:

Here is an example:

SQL> DECLARE Number integer;
  2  BEGIN
  3     WHILE Number < 5 LOOP
  4         DBMS_OUTPUT.PUT_LINE('Number: ' || Number);
  5     END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

To effectively execute a while condition, you should make sure you provide a mechanism for the interpreter to get a referenced value for the condition, variable, or expression being checked. This is sometimes in the form of a variable being initialized although it could be some other expression. Such a while condition could be illustrated as follows:

Boolean Constants

 

Introduction

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false.

In Boolean algebra, something is considered TRUE when it holds a value. Otherwise, it it FALSE.

To support the null value, you can use a constant named NULL. The NULL constant is mostly used for comparison purposes. For example, you can use an IF statement to check the nullity of a variable.

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.