How To Use Oracle OPERATORS

An Oracle operation can be either a single operator and it has only one operand or a binary operator with two operands. The operations in an expression are evaluated in order of operator precedence. In order words an operator can work alone or in pairs and expression will be executed depending on the operators priority.

The most common Oracle operators are following:

Operator	Operation
**	exponentiation
+, –	identity, negation
*, /	multiplication, division
+, -, ||	addition, subtraction, concatenation
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN	comparison
NOT	negation
AND	conjunction
OR	inclusion

The first example is with mathematical operation and it shows how to do some simple arithmetical calculations.

DECLARE
    v_result_1 NUMBER := 1 + 1;
    v_result_2 NUMBER;
    v_result_3 NUMBER := 3;
    v_result_4 NUMBER;
BEGIN
    v_result_2 := 2          - 1;
    v_result_3 := v_result_3 * 2;
    v_result_4 := v_result_3 / 3;
    dbms_output.put_line ('The results are: v_result_1=>'||v_result_1||
                                          ' v_result_2=>'||v_result_2||
                                          ' v_result_3=>'||v_result_3||
                                          ' v_result_4=>'||v_result_4) ;
END;

In the example above we are showing different ways to do operations for example variable v_result_1 gets the value 2 (1 + 1) calculated in the declare area. The v_result_2 gets calculated in the body (2 – 1). Variable v_result_3 gets the initial value in the declare area and the final number is calculated in body (3 * 2). And the last variable v_result_4 is using variable v_result_3 final value (that is 6) to calculate its result in body (6/2).

DECLARE
    v_result_1 NUMBER := 2 + 3 * 2;
    v_result_2 NUMBER := (2 + 3) * 2;
BEGIN
    DBMS_OUTPUT.PUT_LINE ('v_result_1 =>' || v_result_1||
                          ' v_result_2 =>' || v_result_2) ;
END;

The second group of operators are logical operators. The following table shows how are operating AND, OR and NOT operators depending on the value “X” and “Y”. This table should help you to understand the result of any logical operator when you’ll use it.

“X”	“Y”	“X” AND “Y”	“X” OR “Y”	NOT “X”
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

This anonymous PL/SQL block will allow to play through the “X” and “Y” values and see how they match with the table output.

DECLARE
  v_x  BOOLEAN := TRUE;  -- change this value of X
  v_y  BOOLEAN := FALSE; -- change this value of Y

  PROCEDURE show_boolean_text (
    p_name   VARCHAR2,
    p_value  BOOLEAN )
  IS
  BEGIN
    IF p_value IS NULL THEN
      DBMS_OUTPUT.PUT_LINE (p_name || ' = NULL');
    ELSIF p_value = TRUE THEN
      DBMS_OUTPUT.PUT_LINE (p_name || ' = TRUE');
    ELSIF p_value = FALSE THEN
      DBMS_OUTPUT.PUT_LINE (p_name || ' = FALSE');
    ELSE
      DBMS_OUTPUT.PUT_LINE (p_name || ' value is unknown');
    END IF;
  END show_boolean_text;

BEGIN
 show_boolean_text ('X', v_x);
 show_boolean_text ('Y', v_y);
 show_boolean_text ('X AND Y', v_x AND v_y);
 show_boolean_text ('X OR Y', v_x OR v_y);
 show_boolean_text ('NOT X', NOT v_x);
END;
/

This PL/SQL block does not need to be stored and you can run it directly in your database. To change the “X” value amend the variable to (v_x BOOLEAN := FALSE;) or for “Y” (v_y BOOLEAN := TRUE;). Also try the them with the NULL value as (v_x BOOLEAN;) or (v_y BOOLEAN;).

Did you notice that NULL value returns always for the AND operator the not true output. The reason is in the NULL value that is indeterminate – it doesn’t have value. You can’t get any value result exclude the FALSE value from doing operations with the NULL value. That includes all operations except the OR operator that take always a suitable value if it does exist.

The following anonymous PL/SQL blocks will show how operates IF statement with different data types. The next example is with NUMBER type and the output shows 3 times the comparison result. First time both numbers are 1; the second time v_nr1 is 1 and v_nr2 is 2 and on the third time the v_nr1 is 1 and v_nr2 is set to NULL.

DECLARE
  v_nr1  NUMBER;
  v_nr2  NUMBER;

  PROCEDURE show_the_output
  IS
  BEGIN
    IF v_nr1 = v_nr2 THEN  
      DBMS_OUTPUT.PUT_LINE(v_nr1||' is equal '||v_nr2);
    ELSIF v_nr1 != v_nr2 THEN
      DBMS_OUTPUT.PUT_LINE(v_nr1||' is not equal '||v_nr2);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Do not know.');
    END IF;
  END show_the_output;

BEGIN
 v_nr1 := 1;
 v_nr2 := 1;
 show_the_output;

 v_nr1 := 1;
 v_nr2 := 2;
 show_the_output;

 v_nr1 := 1;
 v_nr2 := NULL;
 show_the_output;
END;
/

The following example is almost the same only with the VARCHAR2 type. As on the example above it also has 3 outputs and on first time both variables are ‘aaa’. On the second time v_text1 is ‘aaa’ and v_text2 is ‘bbb’ and on the third time the v_text2 is changed to NULL.

DECLARE
  v_text1    VARCHAR2(3);
  v_text2    VARCHAR2(3);

  PROCEDURE show_the_output
  IS
  BEGIN
    IF v_text1 = v_text2 THEN  
      DBMS_OUTPUT.PUT_LINE(v_text1||' and '||v_text2||' are the same.');
    ELSIF v_text1 != v_text2 THEN
      DBMS_OUTPUT.PUT_LINE(v_text1||' and '||v_text2||' are not the same.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Do not know.');
    END IF;
  END show_the_output;

BEGIN
 v_text1 := 'aaa';
 v_text2 := 'aaa';
 show_the_output;

 v_text1 := 'aaa';
 v_text2 := 'bbb';
 show_the_output;

 v_text1 := 'aaa';
 v_text2 := NULL;
 show_the_output;

END;
/

Inside the IF statement you can do more then comparison and different logical operators are doing comparisons in very different ways. We don’t talk about different logical comparison but executable way. The example below has a local function that returns number 1 but every time when does it executed it also sends text “Function get_number is running.” to screen. The get_number function gets executed in the IF statement and we are doing it two times in both side of the AND operator. Take a look at the output it does show to texts.

DECLARE
  FUNCTION get_number
    RETURN NUMBER
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Function get_number is running.');
    RETURN 1;
  END get_number;

BEGIN
  IF get_number = 1 AND get_number < 2
  THEN
    NULL;
  END IF;
END;
/

Now this example is almost the same as above but with a light change the IF operator has switched to OR. From the output below you will find only one “Function get_number is running.” message.

DECLARE
  FUNCTION get_number
    RETURN NUMBER
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Function get_number is running.');
    RETURN 1;
  END get_number;

BEGIN
  IF get_number = 1 OR get_number < 2
  THEN
    NULL;
  END IF;
END;
/

The OR operator does compare the expressions one by one and it stops when one of them is TRUE. This evaluation is called short-circuit evaluation and it’s good to know to save program execution time and performance. The short-circuit evaluation is used in deterministic clauses and the CASE expression does it.

See Also:
Next: