Lesson 9

By now, you have learned the techniques to query and modify nested tables and varrays. Now that you have completed this module, you should be able to:

1. Insert, update, and delete records from nested tables
2. Insert, update, and delete elements from a varray
3. Explain the way Oracle stores data within nested tables and varrays

Glossary
In this module, you were introduced to the following glossary term:

1. Constructor: Every time a user-defined data type is created, Oracle generates a method called the constructor method, which is a system-defined method. When invoked, the constructor method creates a new object based on the specification of the object type. Oracle names the new constructor method the same name as the new object type. The parameters are the same as the attributes named within the object type definition.

The essential advance made in collections starting with Oracle Database 10g is that the database treats nested tables more like the multisets that they actually are. The database provides high-level set operations that can be applied to nested tables and only, for the time being, to nested tables. Here is a brief summary of these set-level capabilities:

Operation	Return Value	Description
=	BOOLEAN	Compares two nested tables, and returns TRUE if they have the same named type and cardinality and if the elements are equal.
<> or !=	BOOLEAN	Compares two nested tables, and returns FALSE if they differ in named type, cardinality, or equality of elements.
[NOT] IN ()	BOOLEAN	Returns TRUE [FALSE] if the nested table to the left of IN exists in the list of nested tables in the parentheses.
x MULTISET EXCEPT [DISTINCT] y	NESTED TABLE	Performs a MINUS set operation on nested tables x and y, returning a nested table whose elements are in x, but not in y. x, y, and the returned

Operation	Return Value	Description
x MULTISET INTERSECT [DISTINCT] y	NESTED TABLE	Performs an INTERSECT set operation on nested tables x and y, returning a nested table whose elements are in both x and y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table.
x MULTISET UNION [DISTINCT] y	NESTED TABLE	Performs a UNION set operation on nested tables x and y, returning a nested table whose elements include all those in x as well as those in y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table.
SET(x)	NESTED TABLE	Returns nested table x without duplicate elements.
x IS [NOT] A SET	BOOLEAN	Returns TRUE [FALSE] if the nested table x is composed of unique elements.
x IS [NOT] EMPTY	BOOLEAN	Returns TRUE [FALSE] if the nested table x is empty.
e [NOT] MEMBER [OF] x	BOOLEAN	Returns TRUE [FALSE] if the expression e is a member of the nested table x.
y [NOT] SUBMULTISET [OF] x	BOOLEAN	Returns TRUE [FALSE] if the nested table y contains only elements that are also in nested table x.

---

I will make frequent references to this nested table type:

/* File on web: 10g_strings_nt.sql */
TYPE strings_nt IS TABLE OF VARCHAR2(100);

In Oracle Database, "nested tables" are a type of collection that allows storing sets of data, similar to arrays but without fixed boundaries. Starting with "Oracle Database 10g", Oracle introduced the concept of treating nested tables as "multisets". This shift allowed the use of "set-based operations", enabling high-level data manipulation similar to SQL's set operations.
Key Theoretical Concepts
Example Creating and Manipulating Nested Tables as Multisets
Below is an example of using nested tables with MULTISET operations in Oracle:
Step 1: Define a Table and Nested Table Type

-- Define a nested table type
CREATE OR REPLACE TYPE NumberList AS TABLE OF NUMBER;
/

-- Create a table with a nested table column
CREATE TABLE MultisetDemo (
    id NUMBER,
    numbers NumberList
)
NESTED TABLE numbers STORE AS NumberStorage;
/

Step 2: Insert Data into the Table

-- Insert sample data into the table
INSERT INTO MultisetDemo VALUES (1, NumberList(1, 2, 3, 4));
INSERT INTO MultisetDemo VALUES (2, NumberList(3, 4, 5, 6));
/

Step 3: Perform Multiset Operations

-- Query to perform MULTISET UNION DISTINCT
SELECT MULTISET(
    SELECT * FROM TABLE(d1.numbers) 
    UNION DISTINCT 
    SELECT * FROM TABLE(d2.numbers)
) AS union_result
FROM MultisetDemo d1, MultisetDemo d2
WHERE d1.id = 1 AND d2.id = 2;

-- Query to perform MULTISET INTERSECT
SELECT MULTISET(
    SELECT * FROM TABLE(d1.numbers) 
    INTERSECT 
    SELECT * FROM TABLE(d2.numbers)
) AS intersect_result
FROM MultisetDemo d1, MultisetDemo d2
WHERE d1.id = 1 AND d2.id = 2;

-- Query to check if a nested table is a set
SELECT CASE
    WHEN SET(numbers) IS NOT NULL THEN 'Yes, it is a set.'
    ELSE 'No, it contains duplicates.'
END AS is_set
FROM MultisetDemo
WHERE id = 1;

Explanation of Results

• TABLE: Converts the nested table column into a relational table-like format.
• subquery: Selects the nested table column from the parent table.
• Optional WHERE condition: Can be used to further filter elements within the nested table if necessary.

Benefits

• These operations allow complex data processing directly in the database, reducing the need for procedural code.
• Multiset operations improve the expressiveness of SQL in handling advanced data relationships and transformations.

Click the Quiz link below to take a multiple-choice quiz about the material we have covered in this module.
Modifying Nested Tables Varrays - Quiz
In the next module, you will learn how to query and modify large object (LOB) data.