Operators in PL/SQL

Sometimes, we deal with requirements where data from the particular range are to be fetched in the report or an application. Operators in PL/SQL plays an important role to identify selective data from large chunks of transactions.

Operators are inevitable in operations such as unique data retrieval, fetching of data based on its relative existence in other tables and to stop queries from scanning the whole table.

The main use of operators is in WHERE and HAVING clauses. Here the columns are compared with other columns or with specific values to retrieve the results as per the business requirements.

Operators in PL/SQL are classified into below-mentioned categories :

  • Logical Operators
  • Arithmetic Operators
  • Comparison Operators
  • Concatenation Operators
  • Set Operators

Logical Operators in PL/SQL

ANDQuery will execute only if all the conditions in AND clause are true
NOTNOT is a negate operator. It reverses the meaning of an operator with which it is used. (NOT EXISTS, NOT IN etc.)
ORQuery will execute if any of the conditions in OR clause are true.
IS NULLIS NULL compares a column value with a NULL value.
UNIQUEThe UNIQUE operator searches rows of a specified table for unique values.

Comparison Operators in PL/SQL

ANYANY operator is used to compare a value to any value from the list of available values
BETWEEN / NOT BETWEENThe BETWEEN operator is used to search for values that are within a range of values. NOT BETWEEN is the reverse of BETWEEN.
EXISTS / NOT EXISTSThe EXISTS operator is used to search for the presence of data in the specified table. NOT EXISTS is the reverse of EXISTS.
IN / NOT INThe IN operator compares a value to a list of literal values that have been given or retrieved from sub-query. NOT IN is the reverse of IN.
LIKE / NOT LIKEThe LIKE operator compares a value to similar values using wild-card operators. NOT LIKE is the reverse of LIKE.

Arithmetic Operators in PL/SQL

+Addition Operator
Subtraction Operator
*Multiplication Operator
/Division Operator

Concatenation Operators in PL/SQL

||We can concatenate character strings using || operators

Set Operators in PL/SQL

UNIONReturns distinct records from all the queries with which it is connected
UNION ALLAll records from queries with which it is connected are returned
MINUSReturns only those records from the first query which are not in common with the second query
INTERSECTCommon records from queries with which it is connected are returned

If you liked the above post, please leave your comments below.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.