12.4 Querying Collections

   

Now that you know how to get data into a collection, you need a way to get it out. Oracle provides a special TABLE expression just for this purpose.

Prior to release 8i, the TABLE expression was called THE. Only the TABLE expression is used here.


The TABLE expression can be used in the FROM, WHERE, and HAVING clauses of a query to allow a nested table or varray column to be referenced as if it were a separate table. The following query extracts the resupply dates (from the restocks column) that were added previously to the part_c table:

SELECT *  FROM TABLE(SELECT restocks    FROM part_c   WHERE part_nbr = 'GX5-2786-A2'); COLUMN_VALUE ----------- 03-SEP-99 22-APR-00 21-MAR-01

To better illustrate the function of the TABLE expression, the next query retrieves the restocks varray directly from the part_c table:

SELECT restocks  FROM part_c WHERE part_nbr = 'GX5-2786-A2'; RESTOCKS ------------------------------------------------------ RESUPPLY_DATES('03-SEP-99', '22-APR-00', '21-MAR-01')

As you can see, the result set consists of a single row containing an array of dates, whereas the previous query unnests the varray so that each element is represented as a row with a single column.

Since the varray contains a built-in data type rather than an object type, it is necessary to give the varray name so that it may be explicitly referenced in SQL statements. Oracle assigns the varray's contents a default alias of column_value for this purpose. The next example makes use of the column_value alias.

Let's say that you wanted to find all parts resupplied on a particular date. Using the TABLE expression, you can perform a correlated subquery against the restocks varray to see if the desired date is found in the set:

SELECT p1.part_nbr, p1.name FROM part_c p1 WHERE TO_DATE('03-SEP-1999','DD-MON-YYYY') IN  (SELECT column_value FROM TABLE(SELECT restocks FROM part_c p2    WHERE p2.part_nbr = p1.part_nbr)); PART_NBR             NAME -------------------- ------------------------------- GX5-2786-A2          Spacely Sprocket



Mastering Oracle SQL
Mastering Oracle SQL, 2nd Edition
ISBN: 0596006322
EAN: 2147483647
Year: 2003
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net