An operator is 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 second operand from the first	2 – 3 will give  -1
*	Multiplies both operands	2 * 3 will give 6
/	Divides 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). 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 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 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 .