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:
|+, –||identity, negation|
|*, /||multiplication, division|
|+, -, ||||addition, subtraction, concatenation|
|=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN||comparison|
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”|
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.