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
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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!