Associative Arrays in Oracle PL/SQL: The Professional Approach - Deleting a set of elements in an associative array using Oracle PL/SQL (Page 5 of 5 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
