Translate

COLLECTION and COLLECTION METHODS

Some Examples of different types of Collections and Collection Methods:

To Understand COLLECTION and COLLECTION Methods : Click here.
To Understand how to decide which COLLECTION is best for your requirement : Click here.

 Let's see few more examples:
**Creating a varying ARRAY to hold the abbreviations of the days**
DECLARE
Type WArray IS VARRAY(7) OF VARCHAR2(50);
VArray WArray := WArray('Sun','Mon','Tue','Wed','Thu','Fri','Sat');
BEGIN
  FOR i IN 1..VArray.Count
  LOOP
  DBMS_OUTPUT.PUT_LINE(VArray(i));
  END LOOP;
END;
SQL> DECLARE
  2  Type WArray IS VARRAY(7) OF VARCHAR2(50);
  3  VArray WArray := WArray('Sun','Mon','Tue','Wed','Thu','Fri','Sat');
  4  BEGIN
  5    FOR i IN 1..VArray.Count
  6    LOOP
  7    DBMS_OUTPUT.PUT_LINE(VArray(i));
  8    END LOOP;
  9  END;
 10 /
Sun
Mon
Tue
Wed
Thu
Fri
Sat

PL/SQL procedure successfully completed.


**Using COLLECTION Methods on VARRAY**
DECLARE
Type Numlist IS VARRAY(10) of NUMBER;
N Numlist := Numlist(10,20,30,40,50,60);
BEGIN
DBMS_OUTPUT.PUT_LINE('VARRAY Count :'||N.Count);
DBMS_OUTPUT.PUT_LINE('VARRAY Limit :'||N.Limit);
DBMS_OUTPUT.PUT_LINE('VARRAY First :'||N.First);
DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
N.Extend(2,4);
DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
DBMS_OUTPUT.PUT_LINE('VARRAY Limit Element  :'||N(N.Last));
/*Trim last 2 Elements*/
N.Trim(2);
DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
N(6) :=500;
DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N(6));
N.Extend;
N(7) :=600;
DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N(7));

END;
SQL> DECLARE
  2  Type Numlist IS VARRAY(10) of NUMBER;
  3  N Numlist := Numlist(10,20,30,40,50,60);
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE('VARRAY Count :'||N.Count);
  6  DBMS_OUTPUT.PUT_LINE('VARRAY Limit :'||N.Limit);
  7  DBMS_OUTPUT.PUT_LINE('VARRAY First :'||N.First);
  8  DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
  9  N.Extend(2,4);
 10  DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
 11  DBMS_OUTPUT.PUT_LINE('VARRAY Limit Element  :'||N(N.Last));
 12  /*Trim last 2 Elements*/
 13  N.Trim(2);
 14  DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N.Last);
 15  N(6) :=500;
 16  DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N(6));
 17  N.Extend;
 18  N(7) :=600;
 19  DBMS_OUTPUT.PUT_LINE('VARRAY Last  :'||N(7));
 20  END;
 21  /
VARRAY Count :6
VARRAY Limit :10
VARRAY First :1
VARRAY Last  :6
VARRAY Last  :8
VARRAY Limit Element  :40
VARRAY Last  :6
VARRAY Last  :500
VARRAY Last  :600

PL/SQL procedure successfully completed.



**Implementation of ASSOCIATIVE Array**
DECLARE
CURSOR C1 IS
SELECT Name From EMP_TEST;
Counter Number :=0;
Erec C1%ROWTYPE;
/*Declare Array*/
Type NameType IS Table Of EMP_TEST.NAME%TYPE Index By Binary_Integer;
Nmerr NameType;
BEGIN
                  FOR Erec IN C1 LOOP
                  Counter := Counter+1;
                  Nmerr(Counter) := Erec.Name;
                  DBMS_OUTPUT.PUT_LINE('Employee ('||Counter||') :'||Nmerr(Counter));
                  END LOOP;
END;
SQL> DECLARE
  2  CURSOR C1 IS
  3  SELECT Name From EMP_TEST;
  4  Counter Number :=0;
  5  Erec C1%ROWTYPE;
  6  /*Declare Array*/
  7  Type NameType IS Table Of EMP_TEST.NAME%TYPE Index By Binary_Integer;
  8  Nmerr NameType;
  9  BEGIN
 10                    FOR Erec IN C1 LOOP
 11                    Counter := Counter+1;
 12                    Nmerr(Counter) := Erec.Name;
 13                    DBMS_OUTPUT.PUT_LINE('Employee ('||Counter||') :'||Nmerr(Counter));
 14                    END LOOP;
 15  END;
 16
 17  /
Employee (1) :Akhil
Employee (2) :Ankit
Employee (3) :Nikhil
Employee (4) :Rajan
Employee (5) :Karan
Employee (6) :Sajal
Employee (7) :Ravi
Employee (8) :Surya
Employee (9) :Sam
Employee (10) :Jack

PL/SQL procedure successfully completed.



**Implementation of NESTED Table**
DECLARE
CURSOR C1 IS
SELECT Name From EMP_TEST;
Counter Number :=0;
Erec C1%ROWTYPE;
/*Declare NESTED Table*/
Type NameType IS Table Of Varchar2(10);
Nmerr NameType := NameType(); /*Initialize with Empty Constructor*/
BEGIN
                  FOR Erec IN C1 LOOP
                  Counter := Counter+1;
                  Nmerr.Extend;
                  Nmerr(Counter) := Erec.Name;
                  DBMS_OUTPUT.PUT_LINE('Employee ('||Counter||') :'||Nmerr(Counter));
                  END LOOP;
                  DBMS_OUTPUT.PUT_LINE('Total Employees: ' ||Nmerr.Count);
END;
SQL> DECLARE
  2  CURSOR C1 IS
  3  SELECT Name From EMP_TEST;
  4  Counter Number :=0;
  5  Erec C1%ROWTYPE;
  6  /*Declare NESTED Table*/
  7  Type NameType IS Table Of Varchar2(10);
  8  Nmerr NameType := NameType(); /*Initialize with Empty Constructor*/
  9  BEGIN
 10                    FOR Erec IN C1 LOOP
 11                    Counter := Counter+1;
 12                    Nmerr.Extend;
 13                    Nmerr(Counter) := Erec.Name;
 14                    DBMS_OUTPUT.PUT_LINE('Employee ('||Counter||') :'||Nmerr(Counter));
 15                    END LOOP;
 16                    DBMS_OUTPUT.PUT_LINE('Total Employees: ' ||Nmerr.Count);
 17  END;
 18  /
Employee (1) :Akhil
Employee (2) :Ankit
Employee (3) :Nikhil
Employee (4) :Rajan
Employee (5) :Karan
Employee (6) :Sajal
Employee (7) :Ravi
Employee (8) :Surya
Employee (9) :Sam
Employee (10) :Jack
Total Employees: 10

PL/SQL procedure successfully completed.



**Multilevel Collection**
DECLARE
Type arr_type1 IS VARRAY(4) of INTEGER;
Type arr_type2 IS VARRAY(3) of arr_type1;
Varray1 arr_type1 :=arr_type1(10,30,50,70);
Varray2 arr_type2 :=arr_type2(Varray1);
BEGIN
                  FOR i IN 1..Varray1.Count
                  LOOP
                  DBMS_OUTPUT.PUT_LINE('Element['||i||'] :'||Varray1(i));
                  END LOOP;
                  Varray2.Extend;
                  Varray2(2) :=arr_type1(1,3,5,7);

                  FOR i IN 1..Varray2.Count LOOP
                     FOR j IN 1..Varray1.Count LOOP
                       DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] :'||Varray2(i)(j));
                     END LOOP;
                  END LOOP;
END;

SQL> DECLARE
  2  Type arr_type1 IS VARRAY(4) of INTEGER;
  3  Type arr_type2 IS VARRAY(3) of arr_type1;
  4  Varray1 arr_type1 :=arr_type1(10,30,50,70);
  5  Varray2 arr_type2 :=arr_type2(Varray1);
  6  BEGIN
  7                    FOR i IN 1..Varray1.Count
  8                    LOOP
  9                    DBMS_OUTPUT.PUT_LINE('Element['||i||'] :'||Varray1(i));
 10                    END LOOP;
 11                    Varray2.Extend;
 12                    Varray2(2) :=arr_type1(1,3,5,7);
 13
 14                    FOR i IN 1..Varray2.Count LOOP
 15                       FOR j IN 1..Varray1.Count LOOP
 16                         DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] :'||Varray2(i)(j));
 17                       END LOOP;
 18                    END LOOP;
 19  END;
 20  /
Element[1] :10
Element[2] :30
Element[3] :50
Element[4] :70
Element[1][1] :10
Element[1][2] :30
Element[1][3] :50
Element[1][4] :70
Element[2][1] :1
Element[2][2] :3
Element[2][3] :5
Element[2][4] :7

PL/SQL procedure successfully completed.



**Multilevel Associative Array**
DECLARE
Type type1 IS Table OF INTEGER Index By Binary_Integer;
Type type2 IS Table OF type1 Index By Binary_Integer;
arr_tbl1 type1;
arr_tbl2 type2;
BEGIN
FOR i IN 1..2 LOOP
  FOR j IN 1..3 LOOP
                  IF i=1 Then
                    arr_tbl1(j) := j;
                  ELSE
                    arr_tbl1(j) := 4-j;      
                  END IF;
                  arr_tbl2(i)(j) := arr_tbl1(j);
                  DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] : '||arr_tbl2(i)(j));
  END LOOP;
END LOOP;

END;
SQL> DECLARE
  2  Type type1 IS Table OF INTEGER Index By Binary_Integer;
  3  Type type2 IS Table OF type1 Index By Binary_Integer;
  4  arr_tbl1 type1;
  5  arr_tbl2 type2;
  6  BEGIN
  7  FOR i IN 1..2 LOOP
  8    FOR j IN 1..3 LOOP
  9                    IF i=1 Then
 10                      arr_tbl1(j) := j;
 11                    ELSE
 12                      arr_tbl1(j) := 4-j;
 13                    END IF;
 14                    arr_tbl2(i)(j) := arr_tbl1(j);
 15                    DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] : '||arr_tbl2(i)(j));
 16    END LOOP;
 17  END LOOP;
 18  END;
 19  /
Element[1][1] : 1
Element[1][2] : 2
Element[1][3] : 3
Element[2][1] : 3
Element[2][2] : 2
Element[2][3] : 1

PL/SQL procedure successfully completed.



**Using the DELETE Method on a COLLECTION**
DECLARE
Type Numlist is Table of NUMBER;
n Numlist := Numlist(1,2,3,4,5,6,7,8,9,10);
Type Nicklist is Table of Varchar2(50) Index By Varchar2(25);
Nicknames Nicklist;
BEGIN
n.Delete(2);   /*Delete Element 2*/
n.Delete(3,6); /*Delete Element 3 through 6*/
n.Delete(7,7); /*Delete Element 7*/
n.Delete(6,3); /*Does nothing since 6>3*/
n.Delete();    /*Delete all Elements*/

Nicknames('Bob') := 'Robert';
Nicknames('Buffy') := 'Esmerelda';
Nicknames('Chip') := 'Charles';
Nicknames('Dan') := 'Daniel';
Nicknames('Fluffy') := 'Ernestina';
Nicknames('Rob') := 'Robert';
Nicknames.Delete('Chip');/*Delete item with Key*/
Nicknames.Delete('Buffy','Fluffy'); /*Delete Elements between this range*/
END;



**NESTED Table of VARRAY**
DECLARE
Type table_type1 IS VARRAY(3) of INTEGER;
Type table_type2 IS Table of table_type1;
Table_tbl1 table_type1 := table_type1();
Table_tbl2 table_type2 := table_type2(table_tbl1);
BEGIN
FOR i IN 1..2 LOOP
  Table_tbl2.Extend;
  Table_tbl2(i) := Table_type1();
  FOR j IN 1..3 LOOP
    IF i=1 Then
                  Table_tbl1.Extend;
                  Table_tbl1(j) := j;
    ELSE
                  Table_tbl1(j) := 4-j;
    END IF;
    Table_tbl2(i).Extend;
    Table_tbl2(i)(j) := Table_tbl1(j);
    DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] : '||Table_tbl2(i)(j));
  END LOOP;
END LOOP;
END;
SQL> DECLARE
  2  Type table_type1 IS VARRAY(3) of INTEGER;
  3  Type table_type2 IS Table of table_type1;
  4  Table_tbl1 table_type1 := table_type1();
  5  Table_tbl2 table_type2 := table_type2(table_tbl1);
  6  BEGIN
  7  FOR i IN 1..2 LOOP
  8    Table_tbl2.Extend;
  9    Table_tbl2(i) := Table_type1();
 10    FOR j IN 1..3 LOOP
 11      IF i=1 Then
 12  Table_tbl1.Extend;
 13  Table_tbl1(j) := j;
 14      ELSE
 15  Table_tbl1(j) := 4-j;
 16      END IF;
 17      Table_tbl2(i).Extend;
 18      Table_tbl2(i)(j) := Table_tbl1(j);
 19      DBMS_OUTPUT.PUT_LINE('Element['||i||']['||j||'] : '||Table_tbl2(i)(j));
 20    END LOOP;
 21  END LOOP;
 22  END;
 23 /
Element[1][1] : 1
Element[1][2] : 2
Element[1][3] : 3
Element[2][1] : 3
Element[2][2] : 2
Element[2][3] : 1

PL/SQL procedure successfully completed.



**Creating and Accessing Hash Array Collections*/
DECLARE
Cursor C IS
SELECT ID, NAME, SALARY, DEPARTMENT
From EMP_TEST E, DEPT_TEST D
Where E.DEPTNO=D.DEPTNO;

Type total_type IS Table Of NUMBER INDEX BY Varchar2(100);
totals total_type;
Dept Varchar2(50);
BEGIN
FOR rec IN C LOOP
  IF NOT totals.Exists(rec.DEPARTMENT) Then
     totals(rec.DEPARTMENT) := 0;
  END IF;
  totals(rec.DEPARTMENT) := totals(rec.DEPARTMENT) + NVL(rec.SALARY,0);
END LOOP;
Dept := totals.First;
While Dept IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(totals(Dept)||' '||Dept);
  Dept := totals.Next(Dept);
End LOOP;
END;

SQL> DECLARE
  2  Cursor C IS
  3  SELECT ID, NAME, SALARY, DEPARTMENT
  4  From EMP_TEST E, DEPT_TEST D
  5  Where E.DEPTNO=D.DEPTNO;
  6
  7  Type total_type IS Table Of NUMBER INDEX BY Varchar2(100);
  8  totals total_type;
  9  Dept Varchar2(50);
 10  BEGIN
 11  FOR rec IN C LOOP
 12    IF NOT totals.Exists(rec.DEPARTMENT) Then
 13       totals(rec.DEPARTMENT) := 0;
 14    END IF;
 15    totals(rec.DEPARTMENT) := totals(rec.DEPARTMENT) + NVL(rec.SALARY,0);
 16  END LOOP;
 17  Dept := totals.First;
 18  While Dept IS NOT NULL LOOP
 19    DBMS_OUTPUT.PUT_LINE(totals(Dept)||' '||Dept);
 20    Dept := totals.Next(Dept);
 21  End LOOP;
 22  END;
 23  /
0 Communication
234000 ENU
226000 Insurance
112000 Security

PL/SQL procedure successfully completed.



RELATED TOPICS:
Different types of COLLECTIONS and COLLECTION Methods
How to decide which COLLECTION type should be used?

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page. 

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)