| Difference between PL-SQL table and nested table [message #41171] |
Tue, 03 December 2002 22:48  |
Balamurugan.R
Messages: 91
Registered: March 2001
|
Member |
|
|
Hai,
Can anyone explain the difference between
PL-SQL table and nested table. And I want to know
the advantages over both the tables. If u can explain
with example, it will be useful for me.
Thanks in advance.
With Regards,
Balamurugan.R
|
|
|
|
| Re: Difference between PL-SQL table and nested table [message #41173 is a reply to message #41171] |
Wed, 04 December 2002 07:34  |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Balamurugan
They have a few key differences:-
INDEX-BY TABLES
---------------
* These are available to PL/SQL only (i.e. TYPE can only be declared in PL/SQL declaration blocks).
* They do not need to be initialized (this is done as soon as you assign an element).
* They can be initially sparse (e.g assign element 200 will not cause any problems with elements 1 to 199).
* Elements are assigned by reference - you do not need to do anything to "make room" for new elements.
NESTED TABLES
-------------
* These can be SQL (CREATE TYPE... ) or PL/SQL (TYPE nnn IS TABLE OF - you leave off the INDEX BY bit in PL/SQL).
* If you create a SQL type, this can now be used to define a column in a table, just as VARCHAR2, DATE, NUMBER etc. Also, when you create a nested table type, this can be passed as a parameter type from SQL to PL/SQL and back again.
* They must be explicitly initialized (unless you are going to bulk collect straight into them).
* They must be initially dense (can delete elements causing sparseness thereafter).
* They must be explicitly extended when incrementally assigning elements.
WHICH TO USE ?
--------------
I tend to always use index-by tables (and now associative arrays in 9iR2) when I do not need to let SQL have any access to my collection. They are very slightly more efficient than nested tables at volume (in my experience anyway) and much easier to maintain.
Nested tables are VERY useful in PL/SQL when you need to access collections as you would a database table, using the TABLE expression. I tend to use these to define parameter types and pass in a string of values that can then be converted to a collection to use as a "bindable" IN-list.
And nested tables can also be stored as columns, but I don't do this and this is a major learning exercise.
I suggest you read the applications developers guide "Collections and Records" section in the online Oracle documentation at otn.com.
Regards
Adrian
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
