Home

Introduction to Expressions

 

Logical Comparisons

 

Introduction

You can create an expression or expressions that represent a combination of values, variables, and operators. A comparison is a Boolean operation that produces a true or a false result, depending on the values on which the comparison is performed. A comparison is performed between two values of the same type.

Equality Operator =

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

Value1 = Value2

It can be illustrated as follows:

Equalilty Flowchart

 

Not Equal <>

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

Value1 <> Value2

It can be illustrated as follows:

Flowchart: Not Equal

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

Less Than <

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

Value1 < Value2

It can be illustrated as follows:

Flowchart: Less Than

Less Than Or Equal To <=

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:

Flowchart

Greater Than >

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:

Greater Than

The > operator is the opposite to <=.

Greater Than or Equal 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:

Flowchart: Greater Than Or Equal To

The >= operator is the opposite to <.

Conditional Statements

 

Introduction

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

Keyword Expression
	Statement

BEGIN...END; /

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;
/

IF ... THEN ... END IF;

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.

IF ... THEN ... ELSE ... END IF;

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.
 
 
 

CASE ... WHEN ... THEN ... END CASE;

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.

CASE ... WHEN ... THEN ... ELSE ... END CASE;

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.

WHILE ... LOOP ... END LOOP;

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:

WHILE

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:

WHILE

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.

The TRUE and FALSE Constants

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

The NULL Constant

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.

The NOT Operator

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.

 
 
   
 

Home Copyright © 2009-2010 FunctionX, Inc.