An operator is a reserved word that manipulates individual data items and returns a result. The data items are called operands or arguments.

Operators are represented by special characters. For example, the Addition operator is represented by plus (+) and subtraction operator represented by minus (-) etc…

**There are two general classes of operators:**

- Unary operators such as the negation operator (-) operate on one operand.
- Binary operators such as the division operator (/) operate on two operands.

PL/SQL has no ternary operators.

PL/SQL language is rich in built-in operators and provides the following types of operators.

- Arithmetic operators
- Relational operators
- Comparison operators
- Logical operators
- String operators

**Arithmetic operators**

Arithmetic operators perform mathematical operations on numeric operands involved. The + and – operators can also be used in date arithmetic.

Operator | Description | Example |
---|---|---|

+ | Adds two operands | 2 + 3 will give 5 |

– | Subtracts the second operand from the first | 2 – 3 will give -1 |

* | Multiplies both operands | 2 * 3 will give 6 |

/ | Divides the numerator by de-numerator | 2 / 3 will give 0.666 |

** | Exponentiation operator raises one operand to the power of other | 2 ** 3 will give 8 |

**Relational operators**

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator.

Operator |
Meaning |
---|---|

`=` |
equal to |

`<>` , `!=` , `~=` , `^=` |
not equal to |

`<` |
less than |

`>` |
greater than |

`<=` |
less than or equal to |

`>=` |
greater than or equal to |

**Comparison Operators**

Comparison operators compare one expression to another. The result is always true, false, or null.

Operator | Description | Example |
---|---|---|

LIKE | The `LIKE` operator to compare a character, string, or `CLOB` value to a pattern. Case is significant. `LIKE` returns the Boolean value `TRUE` if the patterns match or `FALSE` if they do not match. |
The patterns matched by ename LIKE ‘J%SON’ |

BETWEEN | The `BETWEEN` operator tests whether a value lies in a specified range. It means “greater than or equal to low value and less than or equal to high value. |
41 BETWEEN 30 AND 45 |

IN | The `IN` operator tests set membership. It means “equal to any member of.” The set can contain nulls, but they are ignored. |
DELETE FROM emp WHERE ename IN (NULL, ‘KING’, ‘FORD’); |

IS NULL | The `IS` `NULL` operator returns the Boolean value `TRUE` if its operand is null or `FALSE` if it is not null. Comparisons involving nulls always yield `NULL` . |
IF variable IS NULL THEN … |

**Logical operators**

Logical operators manipulate the results of conditions. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or NULL.

`AND`

and `OR`

are binary operators; `NOT`

is a unary operator.

Operator | Description | Examples |
---|---|---|

AND | Called the logical AND operator. If both the operands are true then condition becomes true. | (A and B) is false. |

OR | Called the logical OR Operator. If any of the two operands is true then condition becomes true. | (A or B) is true. |

NOT | Called the logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false. | not (A and B) is true. |

#### Operator Precedence

The operations within an expression are done in a particular order depending on their *precedence* (priority). The default order of operations from first to last (top to bottom) shown below.

Operator |
Operation |
---|---|

`**` |
exponentiation |

`+` , `-` |
identity, negation |

`*` , `/` |
multiplication, division |

`+` , `-` , `||` |
addition, subtraction, concatenation |

`=` , `<` , `>` , `<=` , `>=` , `<>` , `!=` , `~=` , `^=` , `IS` `NULL` , `LIKE` , `BETWEEN` , `IN` |
comparison |

`NOT` |
logical negation |

`AND` |
conjunction |

`OR` |
inclusion |

Operators with higher precedence are applied first. In the example below, both expressions yield 8 because the division has a higher precedence than addition. Operators with the same precedence are applied in no particular order.

5 + 12 / 4 — > (12 / 4) + 5 ( division has high precedence than addition, so 12 gets divide by 4 and the result will add to 4).

You can use parentheses to control the order of evaluation like (5 + 12) / 4.

**Concatenation Operator**

Double vertical bars (`||`

) serve as the concatenation operator, which appends one string (`CHAR`

, `VARCHAR2`

, `CLOB`

, or the equivalent Unicode-enabled type) to another. For example, the expression.

‘suit’ || ‘case’

returns the following value:

```
'suitcase'
```

If both operands have a datatype `CHAR`

, the concatenation operator returns a `CHAR`

value. If either operand is a `CLOB`

value, the operator returns a temporary CLOB. Otherwise, it returns a `VARCHAR2`

value.

The concatenation operator ignores null operands. For example, the expression

‘apple’ || NULL || NULL || ‘sauce’

returns the following value:

`'applesauce'`

The String operators will be discussed in a later chapter.