What are Operators in Oracle? & What are simple Operators?

Operators in Oracle

Fundamental elements in Oracle SQL, operators are used to change individual data items (also known as arguments or operators) and produce a result. Operators are denoted by keywords or special characters, like the asterisk (*) for multiplication. Generally speaking, operators are categorised according to the kind of operation they carry out, such as set manipulation, arithmetic, comparison, or logic.

Operator Precedence

The sequence in which the Oracle Database analyses various operators within a single expression is determined by precedence. Higher precedence operators are assessed before lower precedence operators. From left to right, operators of equal precedence are assessed. To specifically override the conventional operator precedence in an expression, use brackets.

SQL operators are assessed before SQL conditions in terms of processing order.

Categories of Operators and Examples

Arithmetic, logical/comparison, set, hierarchical, and multiset operators are among the types of operators that Oracle SQL offers.

Arithmetic Operators

Numerical quantities can be multiplied, divided, added, subtracted, and negated using arithmetic operators. One (unary) or two (binary) arguments can be used with them. Exponentiation with the double asterisk (**) is also supported in PL/SQL.

Example: Basic Arithmetic Functions

The following example demonstrates the four basic arithmetic functions (+, -, *, /) using columns Above and Below from a sample table named MATH:

Code Example:

select Name, Above, Below, Empty, Above + Below  AS Plus, Above - Below  AS Subtr, Above * Below  AS Times, Above / Below  AS Divided
from MATH where Name = 'HIGH DECIMAL';

Output:

Comparison and Logical Operators (Conditions)

Comparison conditions compare one expression with another, resulting in TRUE, FALSE, or NULL. These operators are typically used in the WHERE clause to apply logical instructions and restrict the rows returned.

Example: Range Search using BETWEEN and Logical AND

This query finds male customers in Connecticut (CT) who were born between 1936 and 1939.

Code Example:

SQL> select cust_id, cust_gender, cust_year_of_birth
2 from customers
3 where cust_state_province = 'CT'
4 and cust_gender = 'M'
5 and cust_year_of_birth between 1936 and 1939;

Output:

Example: Pattern Search using LIKE

This query retrieves customer last names that contain the string ‘inl’.

Code Example:

SQL> select cust_last_name
2 from customers
3 where cust_last_name like '%inl%';

Output:

Set Operators

A compound query is one that uses set operators to aggregate the output of two enquiries into a single result set. The number of expressions chosen by the component queries must be equal, and the expressions must be from the same datatype group (e.g., character or numeric).

Example: UNION ALL

UNION ALL retrieves all rows, including duplicates of 5 and 6, assuming that table x contains (1, 2, 3, 4, 5, 6) and table y has (5, 6, 7).

Code Example:

SQL> select * from x
2 union all
3 select * from y;

Output:

COL
---
1
2
3
4
5
6
5
6
7
9 rows selected.

Specialized Operators

Concatenation Operator ()

To “glue” two strings together, use the concatenation operator, which is the double vertical bar (||). The output is CHAR (limited to 2000 characters) if both strings are of the CHAR datatype, and VARCHAR2 (limited to 4000 characters) if either is. A temporary CLOB is produced if either of the arguments is a Large Object (CLOB).

Hierarchical Query Operators

Only hierarchical queries can use these operators:

• PRIOR: An expression for the parent row of the current row in the hierarchy is evaluated using the unary operator PRIOR.
• CONNECT_BY_ROOT:  A unary operator that uses information from the hierarchy’s root row to return the value of a column.

Multiset Operators

The results of two nested tables can be combined into one nested table using multiset operators. Oracle Database 10g introduced them. Among these operators are MULTISET EXCEPT, MULTISET UNION, and MULTISET INTERSECT.

Regular Expression Operators

Regular expressions are now supported by text search functions like LIKE as of Oracle Database 10g. While REGEXP_SUBSTR retrieves substrings that match a pattern, REGEXP_LIKE compares regular expressions based on conditions.

Example: REGEXP_SUBSTR (Extracting Area Codes)

This PL/SQL block iterates through a string of phone numbers using REGEXP_SUBSTR, extracting only the area codes (the first subexpression that was caught).

Code Example (partial logic excerpt):

DECLARE
   contact_info VARCHAR2(200) := '... home 773-555-5253 work (312) 555-1234 cell 224.555.2233 ...';
   phone_pattern  VARCHAR2(90) := '(\d{3})\)?[[:space:]\.\-]?\d{3}[[:space:]\.\-]?\d{4}';
   phone_counter NUMBER;
   area_code VARCHAR2(3);
BEGIN
   -- Initialize counter and loop through matches, extracting the 1st subexpression ('i',1)
   phone_counter := 1;
   DBMS_OUTPUT.PUT_LINE('The area codes are:');
   LOOP
      area_code := REGEXP_SUBSTR (contact_info,phone_pattern,1,phone_counter,'i',1);
      EXIT WHEN area_code IS NULL; 
      DBMS_OUTPUT.PUT_LINE(area_code);
      phone_counter := phone_counter + 1;
   END LOOP;
END;
/

Output:

The area codes are:
773
312
224

CASE Expressions

Although the CASE expression is technically an expression rather than a conventional operator, it enables sophisticated conditional logic (if, then, and else) to be used directly within PL/SQL blocks and SQL statements.

Example: CASE Expression Search

This statement determines the average employee wage while making sure that no salary is considered to be less than $2000 for the purposes of the calculation.

Code Example:

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
   ELSE 2000 END) "Average Salary" FROM employees e;

Output:

User-Defined Operators

The CREATE OPERATOR statement is used to build user-defined operators, which are referred to in SQL statements and index types. Like built-in operators, they accept a set of operands as input and output a result.

Example: Creating a User-Defined Operator (eq_op)

First, a function to define the logic is created:

CREATE FUNCTION eq_f(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
BEGIN
   IF a = b THEN RETURN 1;
   ELSE RETURN 0;
   END IF;
END;
/

Then, the operator is created, binding it to the defined function eq_f:

CREATE OPERATOR eq_op
   BINDING (VARCHAR2, VARCHAR2)
   RETURN NUMBER
   USING eq_f;