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 perform mathematical operations on numeric operands involved. The + and – operators can also be used in date arithmetic.
|+||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|
The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator.
||not equal to|
||less than or equal to|
||greater than or equal to|
Comparison operators compare one expression to another. The result is always true, false, or null.
The patterns matched by
ename LIKE ‘J%SON’
||41 BETWEEN 30 AND 45|
||DELETE FROM emp WHERE ename IN (NULL, ‘KING’, ‘FORD’);|
||IF variable IS NULL THEN …|
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.
OR are binary operators;
NOT is a unary operator.
|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.|
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.
||addition, subtraction, concatenation|
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.
Double vertical bars (
||) serve as the concatenation operator, which appends one string (
CLOB, or the equivalent Unicode-enabled type) to another. For example, the expression.
‘suit’ || ‘case’
returns the following value:
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
The concatenation operator ignores null operands. For example, the expression
‘apple’ || NULL || NULL || ‘sauce’
returns the following value:
The String operators will be discussed in a later chapter.