PL/SQL Operators

In the previous tutorials, we have learnt about the variables in PL/SQL. In almost every programming language, we need to do mathematical calculations and other such related operations. To perform these functions efficiently, we need to use Operators.

An operator is a specific symbol (generally falls in the special character category) which commands the compiler or the SQL engine to perform logical as well as mathematical operations.

PL/SQL Operators

PL/SQL programming language provides the following operators:

Arithmetic Operators

The operators under this category are addition (+), subtraction (-), Multiplication (*), Division (/) and Exponentiation (**). Except Exponentiation Operator, others are commonly used. 

Exponentiation Operator
Suppose we have two variables A and B.
Now, A**B will raise B to the power of A.

Example:
3**2 

This will be implemented as 3 to the power of 2 and the result would be obviously 9.

Comparison Operators

Comparison operators are used to compare the value of one expression with the other. The result will lie in either of the three options:  FALSE, TRUE and NULL.

The operators under this section are as follows:

IN: This operator checks in for the membership in a set. If a particular element is present in a set, it will return Boolean TRUE or if its not present in a set then it shall result in a BOOLEAN FALSE.
Eg: ‘p’ in (‘m’,’n’,’o’,’p’), this operator shall result in a Boolean TRUE Value.

IS NULL: This operator returns the BOOLEAN TRUE value if its operand is NULL or empty and FALSE if it is not NULL or empty. Comparisons involving NULL operands or values always yield NULL. 

LIKE: This operator can compare a String, Character and even a Character Large Object (CLOB) value to a given pattern and returns BOOLEAN TRUE if the value matches the pattern and BOOLEAN FALSE if it does not. 

BETWEEN: This operator checks if a value lies in a given range. m BETWEEN a AND z means that m>=a or if m<=z.

Logical Operators

These are the most common types of operators in every programming language. For PL/SQL programming language, these are as follows:

NOT: This operator is also known as the Logical NOT Operator. This is used to change or basically REVERSE the state of the condition. If a value is previously TRUE, then application of this operator will result into FALSE. Hence, this operator reverses the Logical state.

AND: It is also known as Logical AND Operator. The condition becomes TRUE if both the operands on the LHS and RHS are TRUE.

OR: It is also known as Logical OR Operator. The condition becomes TRUE if any of the operands on the LHS or RHS are TRUE.

Relational Operators

The relational operator is used for comparison between two values or expressions and provides the result in terms of Boolean values such as TRUE or FALSE.

These operators are as follows:

Equal to (=): This operator check if the values of two operands on the LHS and RHS of this symbol are equal or not and returns TRUE if its equal.

Less than (<): This operator checks if the operands in the LHS is Less than the one on the RHS and returns TRUE if its Less than the other one.

Greater than (>): This operator checks if the operands in the LHS is Greater than the one on the RHS and returns TRUE if its Greater than the other one.

Less than or Equal to (<=): This operator checks if the operands in the LHS is Less than or equal to the one on the RHS and returns TRUE if its Less than or equal.

Greater than or Equal to (>=): This operator checks if the operands in the LHS is Greater than or equal to the one on the RHS and returns TRUE if its Greater than or equal.

Not equal to: This operator has 3 symbols and its use varies from one situation to another. The operators used for this are: (!=), (<>), (~=).  It checks if the condition is true or not and returns TRUE if the operands are UNEQUAL.

String Operators

PL/SQL offers the concatenation operator (||) for joining two string variables. However, there is also a function named as CONCAT which takes two parameters and returns the appended string.