Oracle Collection - Nested tables

Nested tables

         Nested tables hold an arbitrary number of elements and use sequential numbers as the index or key to the elements. Nested tables can be stored in database tables and manipulated through SQL. They are appropriate for data relationships that must be stored persistently. Nested tables are flexible in that arbitrary elements can be deleted, rather than just removing an element from the end. Note that the order and subscripts (keys) of nested tables are not preserved as the table is stored and retrieved in the database

   Nested tables can be stored in a database column.Nested tables are initially dense, but they can become sparse (Data does not have to be stored in consecutive rows) when elements are deleted. Nested Table is available in PL/SQL as well as in SQL.

Nested tables are dense, but they can become sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts

CREATE OR REPLACE TYPE nestedtable_type AS TABLE OF element_type; (PLSQL Type in SQL)

CREATE TABLE nested_table (id NUMBER, col1 nestedtable_type) NESTED TABLE col1 STORE AS col1_tab;   (Nested Table)

TYPE nestedtable_type IS TABLE OF element_type; (PLSQL Type in PLSQL)

Oracle Collection - PLSQL Table

Collections


    A collection is an ordered group of elements ,of the same type.Collection have only one dimension,we can nodel multi-dimensional arrays by creating collections whose elements are also collections.

The three type of collections are following

1) Associative Array
2) Nested tables
3) Varrays

Associative Array
TYPE array_type IS TABLE OF element_type INDEX BY key_type;
array_name array_type;
array_name(index) := value;


The “Associative Arrays” are also known as “Index-By” tables in PL/SQL.Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements. Associative arrays can be sparse, which means not all elements between two elements need to be defined.Similar to hash table in other language
For instance you can have an element at index -29 and one at index 12 with nothing in between. Homogeneous elements mean every element must be of the same type.
Associative arrays are useful for small to medium sized lookup tables where the array can be constructed in memory ( only , which is destroyed after the session ends) each time a procedure is called or a package is initialized. There is no fixed limit on their size and their index values are more flexible- associative array keys can be negative and/or nonsequential, and associative arrays can use string values instead of numbers .The Associative Array is only available in PL/SQL


type my_tab_t is table of number index by pls_integer/ binary_integer;
type my_tab_t is table of number index by varchar2(4000);
type my_tab_t is table of tab.value%TYPE index by tab.id%TYPE;
Example :
Declare
  cursor c_customer is select cust_name,cust_no,rownum from customers;
  type type_cname is table of customers% rowtype index by binary_integer;
  tab_cname type_cname;
  v_counter number:=0;

begin

for r_customer in c_customer loop
   tab_cname(v_counter) := r_customer.cust_name ;
   v_counter:=v_counter+1;
end loop;

forall i in tab_cname.first .. tab_cname.last
  dbms_output.put_line(tab_cname(i));
end;


PLSQL Table attributes

DELETE - Delete rows in a table.
EXISTS - Return TRUE if the specified entry exists in the table.
COUNT - Returns the number of rows in the table .
FIRST - Returns the index of the first in the table.
LAST - Returns the index of last row in the table.
NEXT - Returns the index of the last row in the table .
PRIOR - Returns the index of previous row in the table before the specified row.

Use NOCOPY hint to reduce overhead of passing collections in and out of program units

-----------