Working With Nested Table Oracle

1. Overview

This document talks about the steps to working with Oracle nested tables.Nested table is awesome feature of Oracle. You can create table inside a table, Which means one column can be treated as a table. Nested tables are faster than the parent child table relationship.

2. Technologies and Tools Used

The following technology has been used to achieve requirement

• Oracle PLSQL

3. Use Case

Assume that there is a requirement to achieve the customer needs by using nested tables .

4. Architecture 

Step 1:

Create Object TYPE:

CREATE OR REPLACE TYPE emp_rec_type AS OBJECT

(

EMPNO NUMBER (4),

ENAME VARCHAR2 (10 BYTE),

JOB VARCHAR2 (9 BYTE),

MGR NUMBER (4),

HIREDATE DATE,

SAL NUMBER (7, 2),

COMM NUMBER (7, 2)

);

Create Table type :

CREATE TYPE emp_table_type AS TABLE OF emp_rec_type;

Create Table with Object type:

 CREATE TABLE dept_details

(     deptno    NUMBER (2),

dname     VARCHAR2 (14 byte),

loc       VARCHAR2 (13 byte),

employees EMP_TABLE_TYPE

)nested TABLE employees store AS emp_tab_index;

emp_tab_index is nested table index

Need to create table and insert data in dept and emp tables (If not exist in db )

CREATE TABLE DEPT

(

DEPTNO   NUMBER (2) CONSTRAINT PK_DEPT PRIMARY KEY,

DNAME    VARCHAR2 (14),

LOC      VARCHAR2 (13)

);

/

CREATE TABLE EMP

(

EMPNO      NUMBER (4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME      VARCHAR2 (10),

JOB        VARCHAR2 (9),

MGR        NUMBER (4),

HIREDATE   DATE,

SAL        NUMBER (7, 2),

COMM       NUMBER (7, 2),

DEPTNO     NUMBER (2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

);

/

INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK’);

INSERT INTO DEPT VALUES (20,’RESEARCH’,’DALLAS’);

INSERT INTO DEPT VALUES (30,’SALES’,’CHICAGO’);

INSERT INTO DEPT VALUES (40,’OPERATIONS’,’BOSTON’);

INSERT INTO EMP VALUES (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,NULL,20);

INSERT INTO EMP VALUES (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);

INSERT INTO EMP VALUES (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);

INSERT INTO EMP VALUES (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);

INSERT INTO EMP VALUES (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);

INSERT INTO EMP VALUES (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,NULL,30);

INSERT INTO EMP VALUES (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,NULL,10);

INSERT INTO EMP VALUES (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,’dd-mm-rr’)-85,3000,NULL,20);

INSERT INTO EMP VALUES (7839,’KING’,’PRESIDENT’,NULL,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,NULL,10);

INSERT INTO EMP VALUES (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);

INSERT INTO EMP VALUES (7876,’ADAMS’,’CLERK’,7788,to_date(’13-JUL-87′, ‘dd-mm-rr’)-51,1100,NULL,20);

INSERT INTO EMP VALUES (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,NULL,30);

INSERT INTO EMP VALUES (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,NULL,20);

INSERT INTO EMP VALUES (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,NULL,10);

/

COMMIT;

Step2:

INSERT into Nested table:

 DECLARE

CURSOR c_dept

IS

SELECT deptno, dname, loc FROM dept;

TYPE l_dept_rec_type IS RECORD

(      DEPTNO   dept.deptno%TYPE,

DNAME    dept.DNAME%TYPE,

LOC      dept.LOC%TYPE

);

TYPE l_dept_tab_type IS TABLE OF l_dept_rec_type;

l_dept_no        l_dept_tab_type;

CURSOR c_emp_list (P_DEPTNO DEPT.DEPTNO%TYPE)

IS

SELECT emp_rec_type (EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

SAL,

COMM)

FROM EMP

WHERE deptno = P_DEPTNO;

l_EMP_list_tab   emp_table_type;

BEGIN

OPEN c_dept;

FETCH c_dept

BULK COLLECT INTO l_dept_no;

CLOSE c_dept;

FOR i IN l_dept_no.FIRST .. l_dept_no.LAST

LOOP

OPEN c_emp_list (l_dept_no (i).deptno);

FETCH c_emp_list

BULK COLLECT INTO l_EMP_list_tab;

CLOSE c_emp_list;

INSERT INTO dept_details (DEPTNO,

DNAME,

LOC,

employees)

VALUES (l_dept_no (i).deptno,

l_dept_no (i).dname,

l_dept_no (i).loc,

l_EMP_list_tab);

END LOOP;

commit;

END;

Step3:

Select Data from nested table:

SELECT ee.empno,

ee.ename,

ee.comm

FROM dept_details dd,

TABLE (employees) ee

WHERE ee.empno = 7788;

Step4:

UPDATE data in Nested Table:

   UPDATE TABLE (SELECT dept_details.employees

FROM dept_details

WHERE dept_details.deptno = 10)

SET comm = 9000

WHERE empno = 7839;

Step5:

Delete from Nested table:

  DELETE TABLE (SELECT dept_details.employees

FROM dept_details

WHERE dept_details.deptno = 10)

WHERE empno = 7839;