| General Information |
A nested table is a table stored within the structure of another table.
You can perform DML operations on nested tables only if they are defined as columns of
a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression,
the collection expression must be a subquery that uses the TABLE function to select the table's nested table column. |
| Data Dictionary Objects |
| collection$ |
type$ |
dba_types |
all_types |
user_types |
|
| System Privileges |
| ALTER ANY TYPE |
CREATE TABLE |
DROP ANY TABLE |
| CREATE TYPE |
CREATE ANY TABLE |
EXECUTE ANY TYPE |
| CREATE ANY TYPE |
DROP ANY TYPE |
UNDER ANY TYPE |
|
| |
| Creating A Collection Based On An Object |
Creation of a User Defined Object Data Type and Collection |
CREATE OR REPLACE TYPE <type_name> AS TABLE OF <user_defined_data_type>;
/ |
CREATE OR REPLACE TYPE
phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(12));
/
SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;
CREATE OR REPLACE TYPE
phone_tab_t AS TABLE OF
phone_t;
/
SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;
col coll_type format a10
SELECT type_name, coll_type, elem_type_owner, elem_type_name
FROM user_coll_types; |
Heap Table Of A User Defined Data Object Type |
CREATE TABLE <table_name>
column_name <user_defined_data_type); |
CREATE TABLE test (
person_id NUMBER(10),
per_phones phone_t);
desc test
set describe depth all
desc test
set describe depth 1
col data_type format a40
SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'TEST'; |
Heap Table Of With A User Defined Data Object Type As A Nested Table |
CREATE TABLE <table_name> (
column_name <user_defined_data_type>)
NESTED TABLE <column_name> STORE AS <name_of_nested_table>; |
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(10),
last_name VARCHAR2(10),
person_phones phone_tab_t,
active_flag VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;
desc person
SELECT table_name
FROM user_tables;
select table_name, table_type, table_type_owner, nested
FROM user_all_tables;
col parent_table_column format a25
SELECT table_name, table_type_name, parent_table_column
FROM user_nested_tables; |
| |
| Inserting Into A Collection
Based On An Object |
Insert Into Parent And Nested Table |
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<table_type_constructor>(<type_constructor(<type_values>))); |
INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(0, 'Test', 'Tester', phone_tab_t(), 'I');
INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(1, 'Dan', 'Morgan', phone_tab_t(phone_t('C', '206-555-1212')), 'A');
INSERT INTO person
(person_id, first_name, last_name, person_phones)
VALUES
(2, 'Helen', 'Lofstrom', phone_tab_t(phone_t('C', '425-555-1212')));
col person_phones format a50
SELECT * FROM person; |
Insert Into Nested Table |
INSERT INTO TABLE (
SELECT <nested_table_column_name>
FROM <table_name>
WHERE <column_name> <condition <value>)
VALUES
(<column_value>, <column_value>); |
INSERT INTO TABLE(
SELECT person_phones
FROM person
WHERE person_id = 1)
VALUES
('H','206-987-6543'); |
| |
| Selecting From A Collection
Based On An Object |
Select From Nested Table |
SELECT <column_name_list> FROM <table_name>; |
| SELECT * FROM person; |
Select From Nested Table With TABLE Function |
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias>; |
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
| Note: Be sure to use the link at page bottom to explore COLLECTION FUNCTIONS |
| |
| Updating A Collection
Based On An Object |
Update All Nested Table Rows |
UPDATE person
SET person_phones =
<table_constructor>(<type_constructor(<column_values>)); |
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
UPDATE person
SET person_phones = phone_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2;
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
Update Selected Nested Table Rows |
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <alias.nested_column_name> <condition> <value>; |
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.a_code = 'W'
WHERE p.p_number = '206-987-6543';
SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
| |
| Deleting From A Collection
Based On An Object |
Delete From Nested Table |
DELETE TABLE (
<nested_select_statment>) <alias>
WHERE <alias.nested_column_name> <condition> <value>; |
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.p_number = '206-987-6543';
SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
| |
| Creating A Collection Based On A VARRAY |
Creation of a User Defined Object Data Type |
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>);
/ |
CREATE OR REPLACE TYPE title_t AS OBJECT (
title_name VARCHAR2(5));
/
SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types; |
VARRAY Creation |
CREATE TYPE TitleList AS VARRAY(<integer>)
OF <data_type>;
/ |
CREATE TYPE TitleList AS
VARRAY(3) OF title_t;
/
SELECT type_name, typecode, incomplete, final, instantiable
FROM user_types;
SELECT parent_table_name, parent_table_column, type_owner, type_name
FROM user_varrays;
-- note there is no record |
Create Heap Table With Nested Varray and Collection |
CREATE TABLE <table_name> (
column_name varray_name); |
DROP TABLE person PURGE;
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25),
last_name VARCHAR2(25),
person_title TitleList,
active_flag VARCHAR2(1))
NESTED TABLE person_title STORE AS title_tab
TABLESPACE uwdata;
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25),
last_name VARCHAR2(25),
person_title TitleList,
active_flag VARCHAR2(1))
TABLESPACE uwdata;
desc person
set describe depth all
desc person
set describe depth 1
col parent_table_column format a30
SELECT parent_table_name, parent_table_column, type_name
FROM user_varrays; |
| |
| Inserting Into A Collection
Based On A VARRAY |
Insert Records into Parent, VARRAY, and Nested Table |
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<varray_constructor>(<varray_values>)
<table_type_constructor>(<type_constructor(<type_values>))); |
INSERT INTO person
(person_id, last_name, person_title, active_flag)
VALUES
(1, 'Dan', 'Morgan', TitleList(title_t('PhD')),'A');
SELECT * FROM person;
INSERT INTO person
(person_id, last_name, person_title, active_flag)
VALUES
(2, 'Tara', 'Havemeyer', TitleList(title_t('MD'),
title_t('RN')), 'I');
SELECT person_id, first_name, last_name, person_title, active_flag
FROM person; |
| |
| Selecting From A Collection
Based On A VARRAY |
Simple Select |
SELECT <column_name_list> FROM <table_name>; |
SELECT person_id, last_name,
person_title, active_flag
FROM person;
col person_title format a40
SELECT person_id, last_name, person_title, active_flag
FROM person; |
Unnesting Using the TABLE Function |
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE(<alias.nested_table_name> <alias> |
SELECT t1.person_id, t1.last_name, t2.*,
t1.active_flag
FROM person t1, TABLE(t1.person_title) t2; |
| |
| Updating A Collection
Based On A VARRAY |
Update all Nested Table rows associated with a single table row |
UPDATE person
SET person_phones =
<table_constructor>(<type_constructor(<column_values>)); |
UPDATE person
SET person_title = TitleList(title_t('DSc'))
WHERE person_id = 1; |
To change the information in a nested table by using the UPDATE statement with a
VARRAY, you cannot update individual elements in the varray. You must update all the elements at once - that is,
replace the varray. |
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <nested_column_name> <condition> <value> |
select * from person;
UPDATE person
SET person_title = TitleList(title_t('RN'),
title_t('MD'))
WHERE person_id = 2;
select * from person; |
| |
| Deleting From A Collection
Based On A VARRAY |
Delete From Nested Table with VARRAY |
DELETE TABLE (
<nested_select_statment>) <alias> |
SELECT * FROM person;
SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2;
SELECT person_id
FROM (
SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2)
WHERE title_name = 'RN';
DELETE person
WHERE person_id IN (
SELECT person_id
FROM (
SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2)
WHERE title_name = 'RN');
SELECT * FROM person;
DELETE person
WHERE person_id = 1; |
| |
| Comparing
Collections |
Collection Comparison |
Collections cannot be directly
compared for equality or inequality.
For instance, the following IF condition is not allowed:
set serveroutput on
DECLARE
TYPE clientele IS TABLE OF VARCHAR2(64);
group1 clientele := clientele('Customer 1', 'Customer 2');
group2 clientele := clientele('Customer 1', 'Customer 3');
BEGIN
-- Equality test causes compilation error
IF group1 = group2 THEN
dbms_output.put_line('Equal');
ELSE
dbms_output.put_line('Not Equal');
END IF;
END;
/
This restriction also applies to implicit comparisons.
For example, collections cannot appear in a DISTINCT,
GROUP BY, or ORDER BY list.
Individual elements, however can be compared.
For example:
DECLARE
TYPE Clientele IS TABLE OF VARCHAR2(64);
group1 Clientele := Clientele('Customer 1', 'Customer 2');
group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
-- Equality test causes compilation error
IF group1(1) = group2(1) THEN
dbms_output.put_line('Equal');
ELSE
dbms_output.put_line('Not Equal');
END IF;
END;
/
and
DECLARE
TYPE Clientele IS TABLE OF VARCHAR2(64);
group1 Clientele := Clientele('Customer 1', 'Customer 2');
group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
-- Equality test causes compilation error
IF group1(2) = group2(2) THEN
dbms_output.put_line('Equal');
ELSE
dbms_output.put_line('Not Equal');
END IF;
END;
/
so ...
DECLARE
TYPE Clientele IS TABLE OF VARCHAR2(64);
group1 Clientele := Clientele('Customer 1', 'Customer 2');
group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
-- Equality test causes compilation error
FOR i IN 1..2
LOOP
IF group1(i) = group2(i) THEN
dbms_output.put_line('Equal');
ELSE
dbms_output.put_line(group1(i) ||
' Is Not The Same As ' || group2(i));
END IF;
END LOOP;
END;
/ |
