| General Information |
|
Note: A nested table is a table stored within the structure of another table. |
| Data Dictionary Objects |
| collection$
|
tab$
|
type$
|
| dba_nested_tables |
all_nested_tables |
user_nested_tables |
| dba_nested_table_cols |
all_nested_table_cols |
user_nested_table_cols |
| dba_source |
all_source |
user_source |
| dba_tables |
all_tables |
user_tables |
| dba_tab_cols |
all_tab_cols |
user_tab_cols |
| dba_types |
all_types |
user_types |
|
| System Privileges |
|
CREATE ANY TABLE |
CREATE TABLE |
DROP ANY TABLE |
|
CREATE ANY TYPE |
CREATE TYPE |
DROP ANY TYPE |
|
| |
Nested Table Example |
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/
desc courselist
col text format a50
SELECT type, text
FROM user_source
WHERE name = 'COURSELIST';
CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
desc department
desc courses_tab
SELECT table_name, nested
FROM user_tables;
set linesize 121
col table_name format a20
col data_type format a30
col table_type_name format a15
col parent_table_column format a10
SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'DEPARTMENT';
SELECT table_name, table_type_owner, table_type_name,
parent_table_column
FROM user_nested_tables; |
Insert into Nested Table |
SELECT
cardinality(courses)
FROM department;
INSERT INTO department
(name, director, office, courses)
VALUES
('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(
'Expository Writing',
'Film and Literature',
'Modern Science Fiction',
'Discursive Writing',
'Modern English Grammar',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel'));
SELECT * FROM department;
SELECT cardinality(courses)
FROM department; |
Update a nested table |
DECLARE
new_courses CourseList :=
CourseList('Expository
Writing',
'Film and Literature',
'Discursive Writing',
'Modern English Grammar',
'Realism and Naturalism',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel',
'20th-Century Poetry',
'Advanced Workshop in Poetry');
BEGIN
UPDATE department
SET courses = new_courses
WHERE name = 'English';
END;
/
SELECT * FROM department;
SELECT cardinality(courses)
FROM department; |
Drop nested table |
SELECT table_name
FROM user_tables;
DROP TABLE courses_tab;
You cannot directly drop the storage table of a nested table.
Instead, you must drop the nested table column using the ALTER
TABLE ... DROP COLUMN clause.
desc department
ALTER TABLE department
DROP COLUMN courses; |
| |
| Table
Unnesting |
Collection Unnesting Demo |
To select data from a nested table column you use the
TABLE function to treat the nested table as columns of a table. This process is called
"collection unnesting".
SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;
SELECT t1.name, t2.*
FROM department t1, TABLE(t1.courses) t2; |
|