Sunday 25 October 2015

How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL

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 keywordNESTED TABLEis 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:




11 comments:

  1. nice explaination of nested table.

    ReplyDelete
  2. Very informative..Nice explanation too 😊

    ReplyDelete
  3. after travelling across different websites, i found this one as crystal clear explanation. Good work

    ReplyDelete
  4. Very informative and nice explanation too. Nicely written in simple langauage

    ReplyDelete
  5. Excellent blog. Helped in my banking project where one customer can have multiple security associated with a loan

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis