Deleting a set of elements in an associative array using Oracle PL/SQL - Oracle

In the previous section, I deleted only individual elements.  In this section, I shall introduce you to deleting a series of elements (pairs) in an associate array.

Let us consider the following code:

declare
      type year_type is table of number index by binary_integer;
      year_profits      year_type;
      tot_profits number := 0;
      i           number;
begin
      year_profits(1990) := 34000;
      year_profits(1991) := 45000;
      year_profits(1992) := 43000;
      year_profits(1996) := 13000;
      year_profits(1998) := 53000;

      year_profits.delete(1991,1995);

      i := year_profits.first;
      while i <= year_profits.last
      loop
            tot_profits := tot_profits + year_profits(i);
            i := year_profits.next(i);
      end loop;

      dbms_output.put_line('Total profits: ' || tot_profits);
      dbms_output.put_line('No. of years excluding years between
1991 and 1995: ' || year_profits.count);

end;

Within the above code, you can understand that it removes all the elements (or pairs) from the associative array, which are in between the keys 1991 and 1995 (both inclusive).

Summary

Until now, I focused on using only BINARY_INTEGER based “keys” for the pairs that are going to be stored in the associative array.  In fact, you can also try using all of these methods with VARCHAR2.

When you work with a VARCHAR2 based indexed, it works very similar to a hash table.  If we embed the whole code in a beautiful package, I am quite sure that it would help you a lot.  We can further extend this with CURSORS, REF CURSORS, OBJECT TYPES, VARRAYS and so on.  Even though I concentrated only on  associative arrays, several of these methods should work with other types of PL/SQL tables as well.  Go ahead and try it. 

In the next article in this series, I shall mix all these methods in a modular fashion and finally implement it in a reusable package.  So be sure to check this website frequently! 

Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com