Collections -Associated Arrays(index by Table),Nested Table,Varrays

Index by Table
============

1.)Declare
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
X number(10):=1;
Cursor C1 is select * from Siddharth;
BEGIN
open C1;
loop
fetch C1 into V1(X);
EXit when C1%notfound;
dbms_output.put_line(V1(X).id||V1(X).name);
X:=X+1;
end loop;
Close C1;
END;
/

2.)Declare
Type T1 is table of number(10) index by binary_integer;--Index by Table(We Cannot extend the Index and store in the database)...
V1 T1;
begin
Select id bulk Collect into V1 from Siddharth;
for X in V1.first..V1.last loop
dbms_output.put_line(V1(X));
end loop;
end;
/

SQL> DECLARE
  2    -- Associative array indexed by string:
  3  
  4    TYPE population IS TABLE OF NUMBER  -- Associative array type
  5      INDEX BY VARCHAR2(64);
  6  
  7    city_population  population;        -- Associative array variable
  8    i                VARCHAR2(64);
  9  
 10  BEGIN
 11    -- Add new elements to associative array:
 12  
 13    city_population('Smallville')  := 2000;
 14    city_population('Midland')     := 750000;
 15    city_population('Megalopolis') := 1000000;
 16  
 17    -- Change value associated with key 'Smallville':
 18  
 19    city_population('Smallville') := 2001;
 20  
 21    -- Print associative array:
 22  
 23    i := city_population.FIRST;
 24  
 25    WHILE i IS NOT NULL LOOP
 26      DBMS_Output.PUT_LINE
 27        ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
 28      i := city_population.NEXT(i);
 29    END LOOP;
 30  END;
 31  /
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001
 
PL/SQL procedure successfully completed.
 
SQL>

Collection Methods
===============
Declare
Type T1 is table of Number(10) index by binary_integer;--Index by Table
V1 T1;
begin
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
dbms_output.put_line(V1.first);
dbms_output.put_line(V1.last);
dbms_output.put_line(V1.next(2));
dbms_output.put_line(V1.prior(4));
dbms_output.put_line(V1.count);
dbms_output.put_line(V1(5));
end;
/

NESTED TABLE
==============

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

VARRAYS
===========

DECLARE
TYPE T1 IS  VARRAY(5) OF INTEGER;
V1 T1;
BEGIN
V1:=T1(10,20,30,40,50);
for i in V1.first..V1.last loop
dbms_output.put_line(V1(i));
end loop;
END;
/
        (OR) -- Constructor can be assigned or declare as you like

DECLARE
TYPE T1 IS  VARRAY(5) OF INTEGER;
V1 T1:=T1(10,20,30,40,50);
BEGIN
for i in V1.first..V1.last loop
dbms_output.put_line(V1(i));
end loop;
END;
/

Here are some guidelines.

Use index by tables when:

• Your program needs small lookups
• The collection can be made at runtime in the memory when the package/ procedure is initialized
• The data volume is unknown beforehand
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
• You do not need to store the collection in the database

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database
• The number of elements of the varray is known in advance
• The data from the varray is accessed in sequence
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray