| General Information |
| A nested table is a table stored within the structure of another table. |
| Data Dictionary Objects |
| collection$
|
type$
|
dba_types |
all_types |
user_types |
|
| System Privileges |
| CREATE TYPE |
CREATE TABLE |
|
CREATE ANY TYPE |
CREATE ANY TABLE |
|
DROP ANY TYPE |
DROP ANY TABLE |
|
Create Types And 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. The examples
that follow are based on the following scenario:
Suppose the database contains a table hr_info with columns department_id, location, and manager_id, and a column of nested table type
people which has last_name, department_id, and salary columns for all the employees of each respective manager: |
CREATE OR REPLACE TYPE people_typ AS OBJECT (
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/
desc people_typ
SELECT text
FROM user_source
WHERE name = 'PEOPLE_TYP';
SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';
CREATE OR REPLACE TYPE people_tab_typ AS TABLE OF people_typ;
/
desc people_tab_typ
SELECT text
FROM user_source
WHERE name = 'PEOPLE_TAB_TYP';
SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';
CREATE TABLE hr_info (
department_id NUMBER(4),
location_id NUMBER(4),
manager_id NUMBER(6),
people people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;
desc hr_info
set describe depth all linenum on indent on
desc hr_info
col data_type format a30
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'HR_INFO'; |
| |
| DML with
Collections |
Insert |
INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(280, 1800, 999, people_tab_typ());
INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(205, 2000, 881, people_tab_typ());
SELECT * FROM hr_info;
-- The following example inserts into the people nested
-- table column of hr_info table's department numbered 280:
INSERT INTO TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 280)
VALUES ('Smith', 280, 1750);
INSERT INTO TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 205)
VALUES ('Smith', 999, 1750);
COMMIT;
SELECT * FROM hr_info;
-- another insert into the people table nested in hr_info
INSERT INTO TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 280)
VALUES ('Jones', 280, 919);
SELECT * FROM hr_info;
SELECT cardinality(people)
FROM hr_info; |
Update All Nested Rows |
UPDATE TABLE(
SELECT <alias.column_name>
FROM <able_name alias>
WHERE <alias.column_name> = <value)
VALUES (<value, value, value>); |
-- Updates department 280's people
nested table:
UPDATE TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 280) p
SET p.salary = p.salary + 100;
SELECT * FROM hr_info; |
Update Selected Nested Row |
Updates only Smith's record |
UPDATE TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 280) p
SET p.salary = p.salary - 500
WHERE p.last_name = 'Smith';
SELECT * FROM hr_info; |
Select
(Collection Unnesting) |
To select data from a nested table column, you use the TABLE function to treat the nested table as
the columns of a table. This process is called collection unnesting.
You could get all the rows from hr_info, which was created in the preceding example, and all the rows from the people nested table column of hr_info using the following
statement. |
SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id; |
|
Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this
statement. |
CREATE TABLE t (
department_id NUMBER(4),
last_name VARCHAR2(25),
salary NUMBER(8,2));
INSERT INTO t
SELECT t1.department_id, t2.last_name, t2.salary
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;
SELECT *
FROM t; |
Delete |
Delete from Department 280's people
nested table |
DELETE TABLE(
SELECT h.people
FROM hr_info h
WHERE h.department_id = 280) p
WHERE p.salary > 1200; |
| |
| Comparing
Collections |
Comparing Collections |
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;
/ |
|