Oracle provides two types of nested tables

Take a closer look at other functions of the Oracle nested table and make a simple arrangement.

Oracle provides two methods to use nested tables:
1. PL/SQL code is used as the extended PL/SQL language. (This section describes the oracle memory table as part of the oracle nested table function)
2. As a physical storage mechanism, it can store collections persistently.

*/

-- Create a test table:

Create table dept
(Deptno NUMBER (2) primary key,
Dname VARCHAR2 (14 ),
Loc VARCHAR2 (13)
);
　　
Create table emp
(Empno NUMBER (4) primary key,
Ename VARCHAR2 (10 ),
Job VARCHAR2 (9 ),
Mgr NUMBER (4) REFERENCES emp,
Hiredate DATE,
Sal NUMBER (7,2 ),
Comm NUMBER (7, 2 ),
Deptno NUMBER (2) REFERENCES dept
);
　　
Insert into dept SELECT * FROM scott. dept;
Insert into emp SELECT * FROM scott. emp;

-- Create type

Create or replace type emp_type AS OBJECT
(Empno NUMBER (4 ),
Ename VARCHAR2 (10 ),
Job VARCHAR2 (9 ),
Mgr NUMBER (4 ),
Hiredate DATE,
Sal NUMBER (7,2 ),
Comm NUMBER (7, 2)
);
　　
Create or replace type emp_tab_type as table of emp_type;

-- Use nested tables

Create table dept_and_emp
(Deptno NUMBER (2) primary key,
Dname VARCHAR2 (14 ),
Loc VARCHAR2 (13 ),
Emps emp_tab_type
)
Nested table emps store as emps_nest;

-- Add constraints to the nested table (here we will not execute this step first, and we will create the constraint after the next test is completed)
-- Alter table emps_nt add constraint emps_empno_unique
-- Nested tables do not support integrity constraints and cannot refer to any other tables or even their own
-- Add data to the nested table. Let's take a look at the differences between the two methods.
Method 1: INSERT
Dept_and_emp
SELECT dept .*,
CAST (
MULTISET (SELECT empno, ename, job, mgr, hiredate, sal,
Comm
FROM
Emp
WHERE emp. deptno
= Dept. deptno) AS emp_tab_type)
FROM
Dept;
-- Oracle also provides a way to remove the nested set and process it like a relational table (EMPS columns can be treated as a table and are naturally connected without connection conditions ):
SELECT d. deptno, d. dname, emp. * FROM dept_and_emp D, TABLE (d. emps) emp;
-- 14 data records are displayed during execution.

Delete from dept_and_emp;

Method 2: insert into dept_and_emp
SELECT dept. *, CAST (MULTISET (SELECT empno, ename, job, mgr, hiredate, sal, comm
FROM
Emp, dept
WHERE emp. deptno
= Dept. deptno) AS emp_tab_type) from dept;

SELECT d. deptno, d. dname, emp. * FROM dept_and_emp D, TABLE (d. emps) emp;
-- The execution result is 56 Records, which is obviously incorrect.

-- The first is to store the data of a dept emp table that meets the where and other connection conditions as a set. The second is to store all emp data without any association conditions.
-- All of them are used as a dept data storage. This writing method is obviously incorrect. If we add the constraints we just mentioned to the nested table, we can prevent such errors.

-- An error will be reported when we add constraints and execute the second insert statement above.
-- We insert data according to the first insert statement above and continue the test below.

-- Update according to the idea that "each row is actually a table:
Update table (SELECT emps FROM dept_and_emp WHERE deptno = 10) SET comm = 100;

-- Insert and delete Syntax:
Insert into table (SELECT emps FROM dept_and_emp WHERE deptno = 10)
VALUES (1234, 'newemp', 'cler', 7782, SYSDATE, 1200, NULL );
　　
Delete from table (SELECT emps FROM dept_and_emp WHERE deptno = 20)
WHERE ename = 'Scott ';

-- In general, data in a nested table (such as emp_nest) must be connected all the time, but cannot be queried separately. However, if necessary, it is acceptable.
-- Hint NESTED_TABLE_GET_REFS is used for EXP and IMP to process nested tables.

SELECT/* + NESTED_TABLE_GET_REFS + */NESTED_TABLE_ID, SYS_NC_ROWINFO $ FROM emps_nest;

-- The NESTED_TABLE_ID and SYS_NC_ROWINFO $ Columns cannot be seen in the EMPS_NEST structure. For the parent table DEPT_AND_EMP, NESTED_TABLE_ID is a foreign key.
-- Use this hint to directly operate the nested table:
UPDATE/* + NESTED_TABLE_GET_REFS + */emps_nest SET ename = INITCAP (ename );
　　
-- Storage of nested tables:
-- In the preceding example, two tables are generated:
/*
DEPT_AND_EMP
(Deptnob NUMBER (2 ),
Dname VARCHAR2 (14 ),
Loc VARCHAR2 (13 ),
SYS_NC0000400005 $,
RAW (16 ))
　　
EMPS_NEST
(SYS_NC_ROWINFO $,
NESTED_TABLE_ID,
RAW (16 ),
Empno NUMBER (4 ),
Ename VARCHAR2 (10 ),
Job VARCHAR2 (9 ),
Mgr NUMBER (4 ),
Hiredate DATE,
Sal NUMBER (7,2 ),
Comm NUMBER (7, 2 ))
*/
-- By default, each nested table column generates an additional RAW (16) Hidden Column and creates a unique constraint on it to point to the nested table. The nested table has two
-- Hide column: SYS_NC_ROWINFO $ is a pseudo column of all scalar elements returned as an object. The foreign key of the other NESTED_TABLE_ID points back to the parent table.
-- You can see the real code:
/*
Create table DEPT_AND_EMP
(Deptno number (2, 0 ),
DNAME VARCHAR2 (14 ),
LOC VARCHAR2 (13 ),
EMPS EMP_TAB_TYPE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
Logging storage (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 freelist group 1
BUFFER_POOL DEFAULT)
TABLESPACE USER
NESTED TABLE EMPS
Store as EMPS_NEST
Return by value;
　　
Return by value describes how a nested table is returned to a customer application.
The NESTED_TABLE_ID column must be indexed. A better solution is to use IOT to store nested tables.
Create table DEPT_AND_EMP
(Deptno number (2, 0 ),
DNAME VARCHAR2 (14 ),
LOC VARCHAR2 (13 ),
EMPS EMP_TAB_TYPE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
Logging storage (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 freelist group 1
BUFFER_POOL DEFAULT) TABLESPACE USER
NESTED TABLE EMPS
Store as EMPS_NEST
(Empno not null,
UNIQUE (empno ),
Primary key (nested_table_id, empno ))
ORGANIZATION
Index compress 1)
Return by value;
　　
In this way, compared with the default nested table, the table uses less storage space and has the most needed indexes.
Why nested tables are not used as permanent storage
1. The additional overhead of the RAW (16) column is added. This additional column is added to both the parent and child tables;
2. When there is already a unique constraint, the unique constraint on the parent table is an additional overhead;
3. Unsupported structure (NESTED_TABLE_GET_REFS) is not used, and nested tables are not easy to use.
It is generally recommended to use nested tables in programming structures and views. If you want to use nested tables as the storage mechanism, make sure that the nested tables are IOT, to avoid additional overhead of indexes in the NESTED_TABLE_ID and nested tables.