# PL/SQL Operators

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 `LIKE` can include two special-purpose characters called wildcards. An underscore (`_`) matches exactly one character; a percent sign (`%`) matches zero or more characters. For example, if the value of `ename` is `'JOHNSON'`, the following expression is true:

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.