Well, We have heard a lot about advanced PL/SQL and particularly about Collection(s) of Oracle. Collection(s) comprise Nested Table, Varray and Index By Tables. Each has its own way of working. Most of time, these Collections are used in PL/SQL program. Eg Function, Procedure, Package.
It is not very often when we see any developer using these collections in SQL statement. Main reason could be because they themselves have not seen actually someone using it. Using Collections in SQL statements add a little more complexity in statement structure so many developer(s) try to skip this and use their old and proven methodology.
In this post , we will discuss exact method to use Nested Table in SQL statement and performance impact on queries using such tables. In first part of this post we will discuss actual working of this and in second post we will discuss performance impact.
Not all three collections could be used in SQL. We can use only Nested Table and Varray in SQL statement. For to be used in SQL statement these Collection / Types should be stored as an object in Database.
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Step:- 1
Define a Object type and Map method. Following, I have created a type of Object Kind and defined a MAP method for comparison purpose.
SQL> CREATE TYPE SCOTT.COURSE_OBJ AS OBJECT
2 (
3 STU_ID NUMBER,
4 COURSE_ID NUMBER,
5 COURSE_NAME VARCHAR2(40),
6 MAP MEMBER FUNCTION MAP1 RETURN NUMBER);
7 /
1 CREATE TYPE BODY SCOTT.COURSE_OBJ AS
2 MAP MEMBER FUNCTION MAP1 RETURN NUMBER IS
3 BEGIN
4 RETURN STU_ID||COURSE_ID;
5 END MAP1;
6* END;
Step 2:- Created a TYPE of Nested Table type and used Course_Obj Object Type that we created in Step 1.
SQL> CREATE TYPE SCOTT.COURSE_NST_TB AS TABLE OF SCOTT.COURSE_OBJ;
2 /
Type created.
Step:- 3 Created a table and reference Nested Table (Scott.Course_Nst_Tb) as a column in table. See the STORE AS clause of the Table definition. We can use USER_TYPES dictionary table to check information
Of TYPES created by user.
SQL> CREATE TABLE SCOTT.STU_COURSE_INFO(STU_ID NUMBER,STU_NAME VARCHAR2(40),COURSE_INFO SCOTT.COURSE_NST_TB)
NESTED TABLE COURSE_INFO STORE AS COURSE_INFO;
Table created.
Elapsed: 00:00:00.29
Insert into Scott.Stu_Course_info
SQL> Insert Into Scott.Stu_Course_info Values (1,'ABC',scott.course_nst_tb(scott.course_OBJ(1,'Eelementry English')));
Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1001,'Elementry Physics')))
Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1002,'Elementry Science')))
Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1003,'Elementry Math')))
Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1004,'Elementry Chemistry')))
Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1005,'Elementry Pyscology')))
Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1001,'Elementry Physics')))
Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1002,'Elementry Science')))
Insert into scott.Stu_course_info values (102,'ABC’,scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1003,'Elementry Math')))
Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1004,'Elementry Chemistry')))
Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1005,'Elementry Pyscology')))
(101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1001,'Elementry Physics')))
Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1002,'Elementry Science')))
Insert into scott.Stu_course_info values (101,'XYZ’,scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1003,'Elementry Math')))
Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1004,'Elementry Chemistry')))
Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1005,'Elementry Pyscology')))
Elapsed: 00:00:00.01
SQL> Commit ;
Commit complete.
Elapsed: 00:00:00.00
SELECT * FROM SCOTT.STU_COURSE_INFO where stu_id='101'
STU_ID STU_NAME COURSE_INFO
101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1001,'Elementry Physics')')
101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1002,'Elementry Science')')
101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1003,'Elementry Math')')
101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1004,'Elementry Chemistry')')
101 XYZ SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1005,'Elementry Pyscology')')
Now! Here lies the little complexity. You cannot see the actual value of a NESTED type column by merely referencing column in SELECT clause.
If we fire any select statement having a column of Nested Type , The result set will be shown just like above.
The correct way to select any column defined as NESTED type follows:
Select sci.stu_id,sci.stu_name,x.* from scott.stu_course_info sci,table(sci.course_info)x
STU_ID STU_NAME STU_ID COURSE_ID COURSE_NAME
---------------------- ---------------------------------------- ---------------------- ---------------------- -------------
103 SBX 103 1001 Elementry Physics
103 SBX 103 1002 Elementry Science
103 SBX 103 1003 Elementry Math
103 SBX 103 1004 Elementry Chemistry
103 SBX 103 1005 Elementry Pyscology
101 XYZ 101 1001 Elementry Physics
101 XYZ 101 1002 Elementry Science
101 XYZ 101 1003 Elementry Math
101 XYZ 101 1004 Elementry Chemistry
101 XYZ 101 1005 Elementry Pyscology
102 ABC 102 1001 Elementry Physics
102 ABC 102 1002 Elementry Science
102 ABC 102 1003 Elementry Math
102 ABC 102 1004 Elementry Chemistry
102 ABC 102 1005 Elementry Pyscology
To select any particular value:
SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X
STU_ID STU_NAME COURSE_ID COURSE_NAME
---------------------- ---------------------------------------- ---------------------- ---------------------- -------------
101 XYZ 1001 Elementry Physics
101 XYZ 1002 Elementry Science
101 XYZ 1003 Elementry Math
101 XYZ 1004 Elementry Chemistry
101 XYZ 1005 Elementry Pyscology
To select any particular value
SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X
WHERE X.STU_ID=SCI.STU_ID
AND X.STU_ID=101
update
To update a table having a column defined as NESTED type, we need to define a MAP method of Object.
1 Update scott.stu_course_info sci
2 set sci.course_info=Scott.Course_Nst_tb(Scott.Course_obj(101,1003,'elementry math'))
3* where course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))
SQL> /
1 row updated.
SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X
WHERE X.STU_ID=SCI.STU_ID
AND X.STU_ID=101
and x.course_name like '%ath%'
STU_ID STU_NAME COURSE_ID COURSE_NAME
---------------------- ---------------------------------------- ---------------------- ---------------------- -------------
101 XYZ 1003 elementry math
Delete
delete from scott.stu_course_info sci
where course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))
commit
SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X
WHERE X.STU_ID=SCI.STU_ID
AND X.STU_ID=101
order by 1,3
STU_ID STU_NAME COURSE_ID COURSE_NAME
---------------------- ---------------------------------------- ---------------------- ---------------------- -------------
101 RAJ 1002 Elementry Science
101 RAJ 1004 Elementry Chemistry
101 RAJ 1005 Elementry Pyscology
As we can see, that it is really not that complicated or difficult to use NESTED type or Varry Type using in SQL Statement. We can use is pretty easily once we start using it. In part 2 we will discuss the performance impact of using NESTED types as columns..
Any suggestion, correction, comments will be appreciated.
No comments:
Post a Comment