Table of contents
1.
Introduction
2.
Arithmetic Operators
3.
Relational Operators
4.
Logical Operators
5.
Comparison Operators
6.
Operators Precedence
7.
FAQs
8.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL - Operators

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

An operator is a symbol instructing the compiler to operate on one or more operands supplied with the symbol. The operand is the variable on which the operation must be performed, and the operator symbol indicates the operation to be performed.

Operators in PL/SQL can be divided into the following categories:

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
     

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Arithmetic Operators

Arithmetic operators are applied to operands to execute various mathematical operations. The various arithmetic operators available in PL/SQL are as follows:

Assume two variables, a and b, respectively, with values of 4 and 2, respectively, to provide examples for each operator.

Operator 

Use

Example

+

Adds the two operands

a + b will give 6

-

Subtracts the second operand from the first operand

a - b will give 2

/

Performs the division operation

a / b will give 2

*

Performs the multiplication operation

a * b will give 8

**

Performs the exponentiation operation where the first operand is raised to the power of the second operand

a ** b will give 16

Relational Operators

When two values are compared, relational operators return the result as a boolean value ( TRUE or FALSE). They're most commonly employed in situations where a comparison is required. The relational operators available in PL/SQL are as follows:

Let's take two variables, a and b, with values of 6 and 10, respectively, to provide examples for each operator.

Operator 

Use

Example

=

To check if the two operands are equal or not

(a=b) is False

!=

<>

~=

These operators are used to determine whether two operands are not equal or have the same values. The condition will return true or false if the operands are not equal.

a!=b is True

<

To check if the LHS value is smaller than the RHS value. 

a<b is True

>

To check if the LHS value is greater than the RHS value. 

a>b is False

<=

To check if the LHS value is smaller than or equal to the RHS value.

a<=b is True

>=

To check if the LHS value is greater than or equal to the RHS value.

a>=b is False

Logical Operators

Logical operators are used to joining two expressions or to define an expression with two operands, and they return True or False depending on the operands or expressions around them.

Assume two variables (or expressions), a and b, with the values false and true, respectively, to provide examples for each operator.

Operator

USE

Example

AND

Return TRUE if both the LHS and RHS operands are true. Returns FALSE if both or either of the LHS and RHS operands is false.

a AND b will return false

OR

Returns TRUE if both or either of the LHS and RHS operands is true. When both the LHS and RHS operands are false, FALSE is returned.

a OR b will return true

NOT

They are applied on a single operand. Returns TRUE when the operand is false. Returns FALSE when the operand is true.

NOT b will return false

Comparison Operators

Comparison operators compare two values and return either TRUE, FALSE, or NULL as a result. The different types of comparison operators are as follows:

Operator

Use

Example

LIKE

This operator matches a single character or group of characters(string). Two wildcard characters are used for matching.

Symbol (%) is used to match a string of any characters.

SELECT * from employee WHERE name LIKE 'A%';

BETWEEN

This operator is used to define whether a value falls within a given range. If the value is within the given range, it returns TRUE; otherwise, it returns FALSE.

SELECT * from students WHERE marks BETWEEN 60 AND 100;

IN

This operator is used when any value type is compared to a given list of values. If the value is found in the given list, it returns TRUE; otherwise, it returns FALSE.

SELECT * from people WHERE city IN ('Delhi,' 'Gujarat', 'Chennai);

IS NULL

This operator returns TRUE if the operand value is NULL(empty); otherwise, it returns FALSE.

SELECT * from student WHERE marks IS NULL;

Operators Precedence

Operator precedence determines the order in which terms in an expression are grouped. This influences how an expression is evaluated. Certain operators take precedence over others; for example, the multiplication operator takes precedence over the addition operator.

For example, x = 6 + 4 * 3; since operator * has higher precedence than +, x is equal to 18, not 30, and here first multiplication takes place, which is 4*3 before being added to 6.

The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

Recommended Topic, DCL Commands in SQL

FAQs

1. What is an operator in PL/SQL?

An operator is a symbol that instructs the compiler to operate on one or more operands supplied with the symbol.
 

2. What are the different operators available in PL/SQL?

Operators in PL/SQL can be divided into the following categories:

  • Arithmetic operators
  • Relational operators
  • Comparison operators
  • Logical operators
     

3. Write an example for BETWEEN operators?

SELECT * from students WHERE marks BETWEEN 60 AND 100;

Key Takeaways

In this blog, we have learned that An operator is a symbol that instructs the PL/SQL compiler to operate on one or more operands. The operand is the variable on which the operation must be performed, and the operator symbol indicates the type of operation required for each operand. We have seen various operators available in PL/SQL with examples.

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass