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

-----------