Oracle Operators: Part 3

Let's continue with the third and the last part of Oracle Operators.

6. Multiset Operator

Combining the result of 2 nested tables into one is the purpose of multiset operators. We can use these operations only on nested tables.

Example:

SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type
FROM DUAL;

There are the following three MULTISET operators.

A. Miltiset Union

The multiset UNION operator compares two nested tables and returns the nested table that includes the combined elements of both of the input tables.

Syntax:

x MULTISET UNION [ DISTINCT ] y

Example:

SELECT Emp_id, Emp_add_ntab
MULTISET UNION Emp_add2_ntab multiset_union
FROM Employees_Demo;

B. Multiset Intersect

The Multiset intersect operator compares two nested tables and returns only those elements that are similar/common in both of the tables.

Syntax:

x MULTISET INTERSECT [ DISTINCT ] y

Example:

SELECT Emp_id, Emp_add_ntab
MULTISET INTERSECT Emp_add2_ntab multiset_union
FROM Employees_Demo;

C. Multiset Except

After comparing both nested tables, it returns a nested table whose elements are in the first input nested table but not in the second.

Syntax:

x MULTISET EXCEPT [ DISTINCT ] y

Example:

SELECT Emp_id, Emp_add_ntab
MULTISET EXCEPT Emp_add2_ntab multiset_union
FROM Employees_Demo;

Note: Here is an example of the use of all three multiset operators.

DECLARE
TYPE nested_table is table of number;
table_1 nested_table := nested_table(1,3,6,4,4,3);
table_2 nested_table := nested_table(1,2,3,1,4,4);
table_3 nested_table ;
num                  NUMBER :=3;

    PROCEDURE display_members (operation varchar2) IS

        members VARCHAR2(2000);
        prefix CHAR;

    BEGIN

        for i in table_3.first .. table_3.last loop
            members := members||prefix||table_3(i);
            prefix := ',';
        end loop;

        dbms_output.put_line('result of table_1 '||operation||' table_2 is '||members);

    END display_members;
  
BEGIN

    table_3 := table_1 MULTISET UNION table_2;
    display_members('multiset union');

    table_3 := table_1 MULTISET UNION DISTINCT table_2;
    display_members('multiset union distinct');

    table_3 := table_1 MULTISET INTERSECT table_2;
    display_members('multiset intersect');
   table_3 := table_1 MULTISET INTERSECT DISTINCT table_2;
    display_members('multiset intersect distinct');
  
    table_3 := table_1 MULTISET EXCEPT table_2;
    display_members('multiset except');

    table_3 := table_1 MULTISET EXCEPT DISTINCT table_2;
    display_members('multiset except distinct');

    table_3 := set (table_2); -- remove duplicates
      
END;

7. User Defined Operator

Oracle8i provides an interface that enables developers to define domain-specific operators and indexing schemes and integrate them into the database server. These operators are known as user-defined operators.

A user-defined operator is a schema object identified by a name that could be a character string or a special character or symbol. Similar to built-in operators, the user-defined operator takes a set of operands as input and returns a result. The implementation of the operator must be provided by the developer or data cartridge writer.

User-defined operators can be invoked anywhere built-in operators can be used, that is, wherever expressions can occur in queries and data manipulation statements, such as:

• The select list of a SELECT statement or sub-query
• The condition of a WHERE clause
• The ORDER BY and GROUP BY clauses

For more info on User Defined Operators please go through the following link:

 Oracle User-Defined Operators

Previous article: Oracle Operators: Part 2