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

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

Comparison Operators in PL/SQL

ANY ANY operator is used to compare a value to any value from the list of available values
BETWEEN / NOT BETWEEN The BETWEEN operator is used to search for values that are within a range of values. NOT BETWEEN is the reverse of BETWEEN.
EXISTS / NOT EXISTS The EXISTS operator is used to search for the presence of data in the specified table. NOT EXISTS is the reverse of EXISTS.
IN / NOT IN The 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 LIKE The 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

UNION Returns distinct records from all the queries with which it is connected
UNION ALL All records from queries with which it is connected are returned
MINUS Returns only those records from the first query which are not in common with the second query
INTERSECT Common 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.

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.