Oracle Programming with PL/SQL Collections
The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.
The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:
DECLARE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE; Fiction_genres genres; BEGIN fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR'); END;
All PL/SQL collections contain a number of built-in methods that prove useful when working with them. Table 1 lists these Collection methods.
|Method||Action It Performs|
|COUNT||Returns number of elements in the Collection|
|EXISTS||Returns Boolean true if element at specified index exists; otherwise, false|
|EXTEND||Increases size of Collection by 1 or number specified, ie. EXTEND(n)|
**Cannot use with Associative Array
|FIRST||Navigates to the first element in the Collection|
|LAST||Navigates to the last element|
|PRIOR||Navigates to the previous element|
|NEXT||Navigates to the next element|
|TRIM||Removes the last element, or the last n elements if a number is specified, ie. TRIM(n) |
**Cannot use with Associative Array
|DELETE||Removes all elements of a Collection, or the nth element, if a parameter is specified|
The following code sample demonstrates how to use a few of these methods. We are using a Varray in the example, but the methods function similarly on all collection types. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration. This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.
--Add a new genre. IF adding_new_genre THEN --Is this genre id already in the collection? IF NOT fiction_genres.EXISTS(v_genre_id) THEN --**Add** another element to the varray. fiction_genres.EXTENDS(1); fiction_genres(v_genre_id) := v_genre; END IF; --Display the total # of elements. DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is : '||fiction_genres.COUNT(); END IF; ... ... --Remove all entries. IF deleting_all_genres THEN Fiction_genres.DELETE(); END IF;
The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.
CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE; / CREATE TABLE book_library ( library_id NUMBER, name VARCHAR2(30), book_genres genres); /
When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:
--Insert a new collection into the column on our book_library table. INSERT INTO book_library (library_id, name, book_genres) VALUES (book_library_seq.NEXTVAL,'Brand New Library', Genres('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'));
The query SELECT name, book_genres from book_library returns us:
NAME BOOK_GENRES -------------------- --------------------------------------------- Brand New Library GENRES('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')
Note how the insertion order of elements in book_genres is retained. When a table contains a Varray type, its data is included in-line, with the rest of the table's data. When a Varray datatype is selected from a database table, all elements are retrieved. The Varray is ideal for storing fixed values that will be processed collectively. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.
Page 2 of 4