Collections in Oracle PL/SQL

 Collections in Oracle PL/SQL

• Index-By Tables (Associative Arrays)
• Nested Table Collections
• Varrays Collections
• Assignments and Equality Tests;

DECLARE

   CURSOR emp_cur
   IS
      SELECT emp_Id, first_name, last_name
        FROM employees
       WHERE department_id = department_id_in;

   TYPE employee_type IS TABLE OF  emp_cur%RowTYPE;
    -- index by works both ways

   l_emp_tt   employee_type;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH emp_cur
      BULK COLLECT INTO l_emp_tt  LIMIT 100;

      EXIT WHEN l_emp_tt.COUNT = 0;
   END LOOP;

--Print 
For idx in 1.. l_emp_tt.COUNT
Loop
  dbms_output.put_line(l_emp_tt(idx).emp_name );
End Loop;

END;
/
-----------------------------------------------------------
----------------------------------------------------------
--Using For Loop
---------------------------------------------------------------
If  not an index by Binary_integer And Using For Loop
--------------------------------------------------------------
Declare 
v_name varchar2(500);
 i pls_integer :=0;
 Cursor cr_cust is select * from customers where cust_gender='F' 
  fetch first 100 rows only;
 
 Type cust_Type is table of cr_cust%RowType ; -- no index by

 Cust_tt Cust_type := cust_type();  ---Then intialize 

 Begin
     
   For cx in cr_cust  
   Loop
    Cust_tt.Extend;
    Cust_tt(cust_tt.count).cust_city := cx.cust_city ;
   End Loop; 
   
  For idx in 1.. cust_tt.count
  Loop
    v_name:= cust_tt(idx).cust_city;
     dbms_output.put_line (v_name);
  End Loop;
  
 End;

----------------------------------------------------------------
Traversing the broken Nested table collection with .Next Keyword
----------------------------------------------------------
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
   
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;