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 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 |
|
comparison |
|
logical negation |
|
conjunction |
|
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 .