All programming languages need arrays. Oracle PL/SQL provides three types. The following post aims to serve as a quick reference and provide some minor examples for each type.
- VARRAY's are the closest to the Pascal or C motion of an array. They are always defined to be of fixed length and start at index 1. VARRAY's can be used on both SQL or PL/SQL and whenever they are stored into the databse, they preserve the order of their elements.
- Nested Tables
Nested tables are much like VARRAY's in the sense that they are single dimensional arrays that can be used in both SQL and PL/SQL, but they do not posses the maximum length limitation. Unlike VARRAY's, nested tables are multisets which means that there is no inherent order to their elements
Both VARRAY's and nested tables must be initialized with the default collection-type constructor, meaning that all declarations of the form
my_collection CollectionTypemust end like
Both VARRAY's and nested tables must be extended to accumulate space for their data. No matter wheither you define the maximum size of an array or not, array slots start with a null value. So you cannot just start assigning values to slots unless you create the appropriate space first. See the examples later on.
Nested tables like their "Associative Array" cousins that we shall discuss shortly are what Steven Feuersteinin refers to as sparse data structures. This means that elements can be removed from nested tables leaving an empty slot in the middle of the array. For a demonstration of the concept, Sse the example on nested tables later on.
- Associative Arrays
- Associative Arrays are single dimensional arrays with a unique index only available in PL/SQL code. Their index can be of any type may that be Integer or Varchar2. Associative Arrays do not need to be initialized using any default constructor. You just declare them and user them.
Declaring a VARRAY can be performed as follows
declare TYPE week_type IS VARRAY(7) of VARCHAR2(30); week_day week_type := week_type();
Declaring a nested table can be performed as follows
DECLARE TYPE string30_table IS TABLE OF Varchar2(30); week_day week_type := tring30_table();
Declaring an associative array can be performed as follows
DECLARE TYPE string30_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; week_day string30_array;
Steven Feuersteinin his excelant Oracle PL/SQL Programming 4th Edition book presents the following example regarding usage of associative arrays.
DECLARE TYPE list_of_names_t IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; happy_family list_of_names_t; l_row PLS_INTEGER; BEGIN happy_family(202020202) := 'Eli'; happy_family(-15070) := 'Steven'; happy_family(-90900) := 'Chris'; happy_family(88) := 'Neva'; l_row := happy_family.FIRST; WHILE l_row IS NOT NULL LOOP DBMS_OUTPUT.put_line( happy_family(l_row)); l_row := happy_family.NEXT(l_row); END LOOP; END;
When dealing with VARRAY's and nested tables then the type definition may be stored at schema level. Again a small example this time from Oracle PL/SQL for Dummies . Notice the different naming styles between the two.
DECLARE TYPE month_nt IS TABLE OF VARCHAR2(50); v_month_nt month_nt := month_nt(); i NUMBER; BEGIN v_month_nt.extend(3); v_month_nt(1) := 'January'; v_month_nt(2) := 'February'; v_month_nt(3) := 'March'; v_month_nt.DELETE(2); DBMS_OUTPUT.PUT_LINE('Count ' || v_month_nt.COUNT); DBMS_OUTPUT.PUT_LINE('Last ' || v_month_nt.LAST); i := v_month_nt.FIRST; LOOP DBMS_OUTPUT.PUT_LINE(v_month_nt(i)); i := v_month_nt.NEXT(i); IF i IS NULL THEN EXIT; END IF; END LOOP; END;
The output of the previous script is ...
Count 2 Last 3 January March
... meaning that we have an array with three slots and a gap between them. I wanted to take this example a little bit further so I changed the definition of the array type to VARRAY., making the program look like this. (Notice the standard FOR loop used for traversing a VARRAY.
DECLARE TYPE month_va IS VARRAY(20) OF VARCHAR2(50); v_month_va month_va := month_va(); i PLS_INTEGER; BEGIN v_month_va.extend(3); v_month_va(1) := 'January'; v_month_va(2) := 'February'; v_month_va(3) := 'March'; DBMS_OUTPUT.PUT_LINE('Count ' || v_month_va.COUNT); DBMS_OUTPUT.PUT_LINE('Last ' || v_month_va.LAST); FOR i IN v_month_va.FIRST .. v_month_va.LAST LOOP DBMS_OUTPUT.PUT_LINE(v_month_va(i)); END LOOP; END;