Methods In Collection Concepts

In Collection Concepts,Methods are built in Procedures or Functions which are used with Dot (.) notation with Collection Type.

We are Having following methods.


1) First

2) Last

3) Count

4) Exists

5) Trim

6) Delete

7) Extend

8) Prior

9) Next

10) Limit



Here Extend ,Trim and Delete are Procedures and rest of the methods are functions.

Here Procedure methods are used to modify the collection and Function methods are used to check the properties of the collection. You ill understand with following example about this meaning .


1) First /Last : is Used to get 1st/Last Element or Subscript in collection.

2) Next/Prior :- Next(n) is used to get next Subscript to Current subscript ,same way Prior(n) is used to get Prior subscript to current subscript.

3) COUNT : We use to get total number of subscripts in collection .It's aways best practice to know while doing coding to know write this method to find how many elements/Subscripts are presented in my current collection.

4) TRIM: It's Used in two ways.
 a) If we use directly TRIM then it ill removes one element from end of the collection.
 b) If we use TRIM(n) then it ill removes 'n' elements from end of the collection.


 Note: Here 'n' should not be Total number of elements of your collection ,Suppose if your collection contains only 5 Subscripts then you can use TRIM(5) only but if you try to use TRIM(6) Then it ill throw "SUBSCRIPT_BEYOND_COUNT" error.

 One more point to remember is ,We cant use TRIM with index by table concept.

5)  Exists: We use EXISTS(n) to check whether 'nth' element exist or not.
We generally use Exist method to avoid error eg: because you may have non existent Subscript in collection so in that case before using non existent element check using "EXISTS" method to find whether that subscript exist in collection or not.
6) EXTEND: WE Used EXTEND(n) to extend 'n' elements/Subscripts in collection.
7) DELETE: We can use this in 3 ways

 a) If we use DELETE Then it removes all elements/Subscripts from collection.

 b) If we use DELETE(n) Then it removes 'nth' element/Subscript from collection.

 c) If we use DELETE(m,n) Then it removes elements from 'mth' element to 'nth' element from collection.


Pls note "Delete" method cant be used with VARRAY because VARRAY is always dense but nested table and index by table can be sparse.

8) LIMIT:
We use rarely this method.If use this method with VARRAY then it returns what's the size of the varray and if we use it with Nested table then it returns NULL because Nested table doesn't have any maximum size.

                      
How can we use this Methods:-
These methods are used in Plsql Collection concepts only not in SQL.

If we have sequential collection subscripts then we can use "FIRST" and "LAST" methods as , collection_name.FIRST..collection_name.LAST to iterate through FOR LOOP.

We can use PRIOR/NEXT to traverse in collection.

Let's have some basic program to understand better on this Collection Methods .

Declare
  Type Emp_Type is VARRAY(15) of Integer;
  V_Emp_Type     Emp_Type;
  V_Emp_Type_New Emp_Type;
  V_Count        Integer;
  V_indx         Integer;
  Cursor C1 IS
    Select Empno from EMP;

Begin

  V_Emp_Type     := Emp_Type();
  V_Emp_Type_New := Emp_Type();
  -- Here we are checking total number of elements in collection
  V_Count := V_Emp_Type.COUNT;
  DBMS_OUTPUT.PUT_LINE(' Total Count ' || V_Count);

  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT
      INTO V_Emp_Type;
    EXIT WHEN C1%NOTFOUND;
  END LOOP;
  -- Here we are checking total number of elements in collection
  V_Count := V_Emp_Type.COUNT;
  DBMS_OUTPUT.PUT_LINE(' Total Count ' || V_Count);
  -- Here we are checking 1st Elemets in collection
  V_Count := V_Emp_Type.FIRST;
  DBMS_OUTPUT.PUT_LINE(' First Element Is ' || V_Count);
  -- Here we are checking last element in collection
  V_Count := V_Emp_Type.LAST;
  DBMS_OUTPUT.PUT_LINE(' Last Element Is ' || V_Count);

  --By Default "V_Emp_Type_New.FIRST " will have Null Value Because you just initialized but didnt extend and allocate any elements for it.
  V_indx := V_Emp_Type_New.FIRST;
  DBMS_OUTPUT.PUT_LINE(' Index/Element for FIRST is ' || V_indx);

  --Here we are allocating 3 elements for this collection
  V_Emp_Type_New.EXTEND(3);
  V_indx := V_Emp_Type_New.FIRST;
  DBMS_OUTPUT.PUT_LINE(' Index/Element for FIRST is-- ' || V_indx);

  WHILE V_indx Is Not NULL LOOP
    DBMS_OUTPUT.PUT_LINE(' Index/Element is ' || V_indx);
    --It ill assign Next element to V_indx variable
    -- and this while loop condition will execute 3 times because we allocated 3 subscripts/elements only.
    --When "V_Emp_Type_New.NEXT(V_indx)" running for 4th time it ill return "NULL" so condition will fail and exit the loop.
    V_indx := V_Emp_Type_New.NEXT(V_indx);
    DBMS_OUTPUT.PUT_LINE(' Next Element Is ' || V_indx);

  END LOOP;

End;

Program 2:

Declare
  Type Emp_Type is VARRAY(150) of Integer;

  V_Emp_Type Emp_Type := Emp_Type();

  V_Count Integer;
  V_indx  Integer := 0;
  Cursor C1 IS
    Select Empno from EMP;

Begin
  --Finding Size of the VARRAY
  DBMS_OUTPUT.PUT_LINE('Element is ' || V_Emp_Type.LIMIT);
  FOR J IN C1 LOOP
    --Allocate elements since its in loop so automatically it ill be allocated until your loop goes.
    V_Emp_Type.EXTEND;
 
    V_indx := V_indx + 1;
    DBMS_OUTPUT.PUT_LINE('Element is ' || V_indx);
    V_Emp_Type(V_indx) := J.EMPNO;
    DBMS_OUTPUT.PUT_LINE('Count After ' || V_Emp_Type.COUNT);
  END LOOP;

  --Trimming element
  DBMS_OUTPUT.PUT_LINE('Count Before Trim ' || V_Emp_Type.COUNT);
  V_Emp_Type.TRIM;

  DBMS_OUTPUT.PUT_LINE('Count After Trim ' || V_Emp_Type.COUNT);
  --Deleting Elements
  DBMS_OUTPUT.PUT_LINE('Count Before Delete ' || V_Emp_Type.COUNT);

  V_Emp_Type.DELETE;

  DBMS_OUTPUT.PUT_LINE('Count After Delete ' || V_Emp_Type.COUNT);
End;