PL/SQL – Operators
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 builtin 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 denumerator  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 Unicodeenabled 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 .
Reply