How do you use arrays in PL/SQL?
In other programming language like Java and C# you simply define as following line:
int[] iArray = new int[5];
But in PL/SQL it’s not as easy as that. Why? Because there are three types of collections and we ought to know which collection type must use. Collections are expecially very useful for caching data of lookup tables. If data is cached then amount of context-switching will be less.
There are three types of collections:
1. Associative Array
This type is alike sequential arrays. The difference is that you have not to use sequential index in associative arrays. Index type can be numeric or alphanumeric.
Syntax,
TYPE table_type_name IS TABLE OF datatype [ NOT NULL ] INDEX BY index_type;
Examples of syntax,
TYPE startdate_type IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE keyval_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
Code example,
DECLARE
TYPE keyval_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
keyval_array keyval_type;
l_row PLS_INTEGER;
BEGIN
keyval_array (’AAA’) := 2;
keyval_array (’BBB’) := 40;
keyval_array (’CCC’) := -7;
DBMS_OUTPUT.put_line ( keyval_array(’AAA’) || keyval_array(’BBB’) || keyval_array (’CCC’) );
END;
Example of for-loop
DECLARE
TYPE keyval_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
keyval_array keyval_type;
elmnt VARCHAR2(20);
BEGIN
keyval_array (’AAA’) := 2;
keyval_array (’BBB’) := 40;
keyval_array (’CCC’) := -7;
elmnt := keyval_array.FIRST;
WHILE (elmnt is not null)
LOOP
IF keyval_array.EXISTS(elmnt) THEN
DBMS_OUTPUT.PUT_LINE ( elmnt || ‘=’ || keyval_array (elmnt) );
END IF;
elmnt := keyval_array.NEXT(elmnt);
END LOOP;
END;
There is various methods you use for associative arrays. Some of these are FIRST/LAST, EXISTS, PRIOR/NEXT, COUNT, DELETE.
At the next post I will try to introduce other two collection types, Nested tables and VARRAYs.
