How To Use Oracle OPERATORS - ORACLE

# How To Use Oracle OPERATORS

###### July 01, 2022 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.