Author:VibinDas
As we all know, Nested Tables (especially the Oracle Collections) can be considered
as one of the advanced concepts in Oracle.
In many instances we may have to deal with Nested
tables and Varrays in our coding
where the requirement may be to save and retrieve a set or a bundle of values
in database columns. One of the benefits of using this way is that, if we have
multiple values to be stored corresponding to one subject or entity, these
multiple values of each particular subject can be treated as a single entity
and stored as a single value in database table.
Sometimes this introduction may
bring a little bit of complexity in your mind; but the real fact is that, this
is a very simple and interesting topic which you are going to enjoy for sure.
Let’s start with an example on
this. Consider a STUDENT table which stores the information like the student
id, first name, last name, his class etc... It’s very pretty simple as
below.
Step 1 : First create a test table as below.
CREATE TABLE TEMP_STUDENT_DTLS
(
STUD_ID NUMBER,
STUD_NAME VARCHAR2(30),
STUD_CLASS NUMBER
);
Step 2 : Insert some entries to it.
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)
VALUES(100, 'JAMES', 10);
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)
VALUES(200, 'ALBERT', 8);
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)
VALUES(300, 'MIKE', 5);
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,STUD_NAME,STUD_CLASS)
VALUES(400, 'THOMAS', 2);
COMMIT;
**** 4 Rows inserted
and committed.
Step 3 : Verifying the inserted records.
STUD_ID
|
STUD_NAME
|
STUD_CLASS
|
100
|
JAMES
|
10
|
200
|
ALBERT
|
8
|
300
|
MIKE
|
5
|
400
|
THOMAS
|
2
|
So far everything is fine here.
No weird things. But it’s going to come soon.
Suppose the requirement has got
changed slightly and the additional thing is as follows
·
Need to store the
subject list and corresponding mark of each student as well in the table.
Do you see anything fishy here?
If each of the students are having same number of subjects, then it would been
somewhat possible - for example, if
there are only 3 subjects for all students, we could have created 6 more
columns such as SUB1, MARK1, SUB2, MARK2, SUB3, MARK3. But here since each of
these students are in different classes, the number of subjects won’t be the
same for everyone. For example, James being in 10th standard will have 12
subjects to study whereas Thomas will have only 4 subjects as he is in 2nd
standard.
Here two approaches are in hand - One is to go for normalization and the second to go with Nested table
concepts. If we are in a position not to split the table further, we can’t go
for normalization. Now the only solution left is to go with Nested tables.
Solution Using Nested Table
Data Loading
Since we need to store the (Subject,
Mark) pair information corresponding to each subject, we need to first declare
a Type object which is capable of storing Subject name and Mark as single.
Step 1 : Creation of an OBJECT Type
CREATE TYPE OBJ_TYPE_SUBJ_MARK IS OBJECT
(
SUBJECT_NAME VARCHAR2(30),
MARK NUMBER
);
Step 2 : Creation of a Nested Table Type
CREATE TYPE NT_SUBJ_MARK_DTLS IS TABLE OF
OBJ_TYPE_SUBJ_MARK;
By doing this, a one
dimensional array ‘NT_SUBJ_MARK_DTLS’ is created where each cell can carry
(SUBJECT, MARK) value combination.
NT_SUBJ_MARK_DTLS
|
(SUBJECT_NAME1,
MARK1)
|
(SUBJECT_NAME2,
MARK2)
|
(SUBJECT_NAME3, MARK3)
|
(SUBJECT_NAME4,
MARK4)
|
(SUBJECT_NAME5,
MARK5)
|
(SUBJECT_NAME6,
MARK6)
|
.
|
.
|
.
|
.
|
Actually there is no restriction on the size of this one dimensional
array. For the time being, showed 6 elements only. However it can be any of any
size according to the number of values we insert into the column.
Step 3 : Recreation of table with one column as this newly created Nested
Table
Now recreate the same table with one additional column which will carry the
Subject and Mark details of each student in an array format. For that, the
column should be having data type of “NT_SUBJ_MARK_DTLS” as given below.
DROP TABLE TEMP_STUDENT_DTLS PURGE;
CREATE TABLE TEMP_STUDENT_DTLS
(
STUD_ID NUMBER,
STUD_NAME VARCHAR2(30),
STUD_CLASS NUMBER,
MARK_DTLS NT_SUBJ_MARK_DTLS
) NESTED TABLE MARK_DTLS STORE AS SYS_GEN_TBL;
Datatype of column is NT_SUBJ_MARK_DTLS which
is nothing but the Nested table we created
Here the keyword ‘NESTED TABLE’ is used to inform Oracle that the column is having data type of a Nested
table (which we have already created). One additional point to remember here is
that, while declaring a database column of such a user defined data type of
Nested Table, internally the contents of that column are not stored along with
the remaining table column values. Instead, they are stored in another System
generated table space allocated by Oracle which we need to name and in our
example, the name ‘SYS_GEN_TBL’ has been given for that. This has to be given
followed by the keyword ’STORE
AS
Step 2 : Insert some entries to it.
Suppose the Subject- Mark details of each of the students are as
follows.
·
James
Marks 45, 40, 35 and 42 for Subjects Physics, Maths,
English and Literature respectively.
·
Albert
Marks 48, 33 and 50 for Subjects Chemistry, Maths, and
Latin respectively.
·
Thomas
Marks 38 and 42 for Subjects History and Maths
respectively.
Hence the entries are like
these.
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,
STUD_NAME,
STUD_CLASS,
MARK_DTLS)
VALUES(100,
'JAMES',
10,
NT_SUBJ_MARK_DTLS(
OBJ_TYPE_SUBJ_MARK ('PHYSICS', 45),
OBJ_TYPE_SUBJ_MARK ('MATHS', 40),
OBJ_TYPE_SUBJ_MARK ('ENGLISH', 35),
OBJ_TYPE_SUBJ_MARK ('LITERATURE', 42)
)
);
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,
STUD_NAME,
STUD_CLASS,
MARK_DTLS)
VALUES(200,
'ALBERT',
8,
NT_SUBJ_MARK_DTLS(
OBJ_TYPE_SUBJ_MARK ('CHEMISTRY', 48),
OBJ_TYPE_SUBJ_MARK ('MATHS', 33),
OBJ_TYPE_SUBJ_MARK
('LATIN', 50)
)
);
INSERT INTO TEMP_STUDENT_DTLS(STUD_ID,
STUD_NAME,
STUD_CLASS,
MARK_DTLS)
VALUES(400,
'THOMAS',
2,
NT_SUBJ_MARK_DTLS(
OBJ_TYPE_SUBJ_MARK ('HISTORY', 38),
OBJ_TYPE_SUBJ_MARK ('MATHS', 42)
)
);
COMMIT;
*****3 Rows inserted and committed.
Step 3 : Verifying the inserted records.
SELECT * FROM TEMP_STUDENT_DTLS;
STUD_ID
|
STUD_NAME
|
STUD_CLASS
|
MARK_DTLS
|
100
|
JAMES
|
10
|
(DATASET)
|
200
|
ALBERT
|
8
|
(DATASET)
|
400
|
THOMAS
|
2
|
(DATASET)
|
As can be seen, the multiple
Subjects and their corresponding marks have been loaded to a single column.
Thus there is no need of any replication of same student record for each and
every subject.
Data Retrieval
·
SELECT
In order to get the subject-mark details of James, we need to use the
TABLE operator as below.
SELECT *
FROM TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
);
SUBJECT_NAME
|
MARK
|
PHYSICS
|
45
|
MATHS
|
40
|
ENGLISH
|
35
|
LITERATURE
|
42
|
·
INSERT
On the other hand, if our
intention is add to add one more subject-mark details to James, then
INSERT INTO TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
)
VALUES('CHEMISTRY',46);
COMMIT;
**** 1 Row
inserted and committed
.
To get the subject-mark details
of James,
SELECT *
FROM TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
);
SUBJECT_NAME
|
MARK
|
PHYSICS
|
45
|
MATHS
|
40
|
ENGLISH
|
35
|
LITERATURE
|
42
|
CHEMISTRY
|
46
|
·
DELETE
In order to delete the
subject = ‘CHEMISTRY’ record,
DELETE
FROM TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
)
WHERE SUBJECT_NAME = 'CHEMISTRY';
COMMIT;
1 Row deleted and committed
To
get the subject-mark details of James,
SELECT *
FROM TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
);
SUBJECT_NAME
|
MARK
|
PHYSICS
|
45
|
MATHS
|
40
|
ENGLISH
|
35
|
LITERATURE
|
42
|
·
UPDATE
In order to update the mark
of subject = ‘PHYSICS’ record from
current value of 45 to 50,
UPDATE TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
)
SET MARK = 50
WHERE SUBJECT_NAME = 'PHYSICS';
COMMIT;
1 Row update and committed
To
get the subject-mark details of James,
SELECT *
FROM TABLE(
SELECT MARK_DTLS
FROM TEMP_STUDENT_DTLS
WHERE STUD_NAME = 'JAMES'
);
SUBJECT_NAME
|
MARK
|
PHYSICS
|
50
|
MATHS
|
40
|
ENGLISH
|
35
|
LITERATURE
|
42
|
Since
we are making use of NESTED Table, the details are saved in the database itself
as database table columns.
If you like this post, please share it on google by clicking on the
Google +1 button.
Please go through similar Oracle Posts
@DWHLAUREATE:
