Oracle Programming with PL/SQL Collections, Page 3
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table. Let's rework our earlier example using a Nested Table. First, you declare your type:
CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE; /
The 'IS TABLE OF' syntax was also used when declaring a PL/SQL Table. However, this declaration omits the 'INDEX BY BINARY_INTEGER' clause required by the former type. Note that we have not specified the size of the collection. This is because Nested Tables, unlike the Varray, require no size specification. In other words, they are unbound. Here is a definition for the book_library database table, which now contains a Nested Table column:
CREATE TABLE book_library ( library_id NUMBER, name VARCHAR2(30), book_genres_tab genres_tab) NESTED TABLE book_genres_tab STORE AS genres_table; /
As stated earlier, a Varray's contents are stored in the same table as the other columns' data (unless the collection is exceedingly large, then Oracle stores it in a BLOB, but still within the same tablespace). With Nested Tables, a seperate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.
--Insert a record into book_library, with a Nested Table of book genres. INSERT INTO book_library (library_id, name, book_genres_tab) VALUES (book_library_seq.NEXTVAL,'Brand New Library', genres_tab('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')); / --Declare a nested table type DECLARE updated_genres_tab genres_tab; BEGIN updated_genres_tab := genres_tab('FICTION','NON-FICTION','HISTORY','BUSINESS AND FINANCE', 'SCIENCE','PERIODICALS','MULTIMEDIA'); --Update the existing record with a new genres Nested Table. UPDATE book_library SET book_genres_tab = updated_genres_tab; END; /
These examples show an insert and an update to the book_table and are similar to what you might see if you were working with a Varray. Both Nested Tables and Varrays allow you to use SQL to select individual elements from a collection. However, Nested Tables have an advantage over Varrays in that they allow for inserts, updates, and deletes on individual elements. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.
To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.
--1.)Select all genres from library 'Brand New Library' that are like '%FICTION%'. SELECT column_value FROM TABLE(SELECT book_genres_tab FROM book_library WHERE name = 'Brand New Library') WHERE column_value LIKE '%FICTION%'; / COLUMN_VALUE ------------------------------ FICTION NON-FICTION --2.)Update entry 'MULTIMEDIA' to a new value. Only possible with a nested table!! UPDATE TABLE(SELECT book_genres_tab FROM book_library WHERE name = 'Brand New Library') SET column_value = 'MUSIC AND FILM' WHERE column_value = 'MULTIMEDIA'; --3.)Select all book_genre_tab entries for this library. SELECT column_value FROM TABLE(SELECT book_genres_tab FROM book_library WHERE name = 'Brand New Library'); COLUMN_VALUE ------------------------------ FICTION NON-FICTION HISTORY BUSINESS AND FINANCE SCIENCE PERIODICALS MULTIMEDIA
The first of the preceding three statements simply selects an individual element from the Nested Table, book_genres_tab, in our book_library database table. The second statement performs an update on an individual element, something possible only with Nested Tables. The last query shown selects all Nested Table elements from the parent table. This demonstrates an important feature of the TABLE operator. An ealier query we performed on a database column of type Varray returned a single comma-delimited list of values ('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'). Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.