PL/SQL Operators

An operator is a symbol that specifies an action to perform in an expression, for example, the ‘+’ symbol in the expression ‘1 + 2’ would add the two numbers together to produce a result. This type of operator is known as an arithmetic operator, however, there are also other groups of operators within PL/SQL that will be explained below.

Arithmetic Operators

Arithmetic operators allow for mathematical calculations to be carried out within a PL/SQL block.

Operator Description
+ Adds two values together.
– Subtracts one value from another.
* Multiplies two values together.
/ Divides one value by another.
** Exponentiation operator, raises one value to the power of another.

The following examples show each of these operators in action.

DECLARE
   -- Variables to use in expressions.
   v1 NUMBER(2) := 10;
   v2 NUMBER(2) := 20;
   v3 NUMBER(1) := 5;
BEGIN
   -- Examples output to the screen.
   DBMS_OUTPUT.PUT_LINE (v1 + v2); -- Addition.
   DBMS_OUTPUT.PUT_LINE (v2 - v1); -- Subtraction.
   DBMS_OUTPUT.PUT_LINE (v1 * v2); -- Multiplication.
   DBMS_OUTPUT.PUT_LINE (v2 / v1); -- Division.
   DBMS_OUTPUT.PUT_LINE (v1 ** v3); -- Exponentiation.
END;

The resulting output from the above is shown below.

30
10
200
2
100000

Comparison Operators

Comparison operators compare values or expressions and are used, for example, in ‘IF’ statements, which are described in the next section on Decision Making. The result of the comparison is either True, False or Null.

Operator Description
= Evaluates to True if the two values are equal.
<> Evaluates to True if the two values are not equal.
!= Evaluates to True if the two values are not equal.
> Evaluates to True if the first value is greater than the second.
>= Evaluates to True if the first value is greater than or equal to the second.
< Evaluates to True if the first value is less than the second.
<= Evaluates to True if the first value is less than or equal to the second.
LIKE Compares a string to another string or pattern and evaluates to True if they match.
BETWEEN Evaluates to True if a value is within a specified range.
IN Evaluates to True if a value exists within a specified set of values.
IS NULL Evaluates to True if a value is equal to null.

String Operators

String operators, as their name suggests, are for use with string values.

Operator Description
LIKE Compares one string to another string or pattern and evaluates to True if they match.
|| Concatenates two strings together.

Logical Operators

Logical operators are used where you want to test more than one condition at the same time, for example, in an ‘IF’ statement, which is discussed in the next section on Decision Making.

Operator Description
AND Logical AND operator where values or expressions on both sides must be True to evaluate to True.
OR Logical OR operator where only one of the two values or expressions must be True to evaluate to True.
NOT Logical NOT operator that can be used in conjunction with ‘AND’ or ‘OR’ to reverse the resulting value. In the case of ‘AND’, if both conditions evaluate to True, then with the use of the ‘NOT’ operator the result would be False.

Operator Precedence

If there are multiple operators in an expression, then there is a priority or order in which they are executed. This is shown below.

Operator Description
** Exponentiation.
*, / Multiplication, division.
+, -, || Addition, subtraction, concatenation.
=, <, >, <=, >=, <>, !=, IS NULL, LIKE, BETWEEN, IN Comparison operators.
NOT Logical NOT operator.
AND Logical AND operator.
OR Logical OR operator.

This order of precedence can however be overridden with the use of brackets around a particular part or parts of an expression.