|
Chapter 19 Nested Tables and VARRAYs |
|
I've been working with Oracle's SQL for more than ten years and PL/SQL for more than five. My brain has rarely turned as many cartwheels over SQL's semantics as it has recently, when contemplating the "collection pseudo-functions" introduced in Oracle8.
These collection pseudo-functions exist to coerce database tables into acting like collections, and vice versa. Because there are some manipulations that work best when data are in one form versus the other, these functions give application programmers access to a rich and interesting set of structures and operations.
Note that these operators are not available in PL/SQL proper, but only in SQL. You can, however, employ these operators in SQL statements which appear in your PL/SQL code, and it is extremely useful to understand how and when to do so. We'll get to some examples shortly.
The four collection pseudo-functions are as follows:
Maps a single column value in a single row into a virtual database table. This pseudo-function allows you to manipulate the elements of a persistent collection.
Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY into a nested table.
Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.
Maps a collection to a database table. This is the inverse of MULTISET.
Oracle has introduced these pseudo-functions in order to manipulate collections that live in the database. They are important to our PL/SQL programs for several reasons, not the least of which is that they provide an incredibly efficient way to move data between the database and the application.
Yes, these pseudo-functions can be puzzling; but if you're the kind of person who gets truly excited by arcane code, these Oracle8 SQL extensions will make you jumping-up-and-down silly.
If you have a column that's a nested table, and you want to insert, update, or delete from the contents of this column, you cannot do so with any SQL statement that is familiar to you from Oracle7. Instead, you will need to use the strangely named keyword THE, which helps tell Oracle which row from the outer table you want to deal with.
Earlier, we created the color_models table:
CREATE TABLE color_models ( model_type VARCHAR2(12), colors Color_tab_t) NESTED TABLE colors STORE AS color_model_colors_tab;
We had inserted a row with model_type ='RGB' and a colors column containing ('RED', 'GREEN', 'BLUE'). Imagine now that we've populated color_models with a half dozen or so records.
One question that might have come into your mind is: how can we retrieve all of the colors for a single model using a SELECT statement?
SELECT VALUE(c) FROM THE(SELECT colors FROM color_models WHERE model_type = 'RGB') c;
OK, you can exhale now. The meaning of this statement is "retrieve the individual elements of RGB color model." Or, more literally, "retrieve the value of each element of the colors nested table within the color_models outer table." Sure enough, it displays the following:[ 3 ]
[3] As of Oracle 8.0.4.
VALUE(C) ------------------------------ RED GREEN BLUE
I guess it's really not that weird; we're just substituting a subquery for a table in the FROM clause.
Why does the language use the keyword name THE instead of, say, OUTER? Oracle's documentation says "THE ( select expression ) identifies the nested table on which the DML operation is to be performed." I guess somebody at Oracle has a sense of humor!
Another way you could have expressed the previous query is to use the predefined alias COLUMN_VALUE:
SELECT COLUMN_VALUE FROM THE(SELECT colors FROM color_models WHERE model_type = 'RGB');
COLUMN_VALUE is a way of referring to elements of a nested table of scalars. It is a syntactic shortcut to achieve the same result as the previous example.
The general structure of a SELECT statement with THE is shown here:
SELECT <
expression list
> FROM THE(SELECT
<outer column name>
FROM
<outer table>
-- which outer table row? WHERE
<condition on outer table>
) WHERE
<condition on inner table>
;
Where:
Is COLUMN_VALUE or a SELECT-list, depending on whether the retrieved collection is a scalar or a composite. If it's a composite (such as an object), then the Select-list can include any of the object type's attributes.
Refers to the name you've given the outer table column, which is a collection type.
The WHERE condition on the outer table must retrieve zero or one row(s); otherwise, you'll get the message "ORA-01427: single-row subquery returns more than one row."
You can also use a THE subquery as the target of an INSERT, UPDATE, and DELETE statements. Some brief examples:
-- change BLUE to BURGUNDY inside the collection UPDATE THE(SELECT colors FROM color_models WHERE model_type = 'RGB') SET COLUMN_VALUE = 'BURGUNDY' WHERE COLUMN_VALUE = 'BLUE'; -- add a silly extra color INSERT INTO THE(SELECT colors FROM color_models WHERE model_type = 'RGB') VALUES ('EXTRA-COLOR'); -- show the current colors SELECT COLUMN_VALUE FROM THE(SELECT colors FROM color_models WHERE model_type = 'RGB'); COLUMN_VALUE ------------------- RED GREEN BURGUNDY EXTRA-COLOR -- delete the extra color DELETE THE(SELECT colors FROM color_models WHERE model_type = 'RGB') WHERE COLUMN_VALUE = 'EXTRA-COLOR';
The Oracle8 Server Application Developers Guide is a good source of more sample DML on collections.
The new THE pseudo-function does not work directly on VARRAYs. However, it is possible to use Oracle8's CAST function on a VARRAY so we can emulate the SELECT statement shown in the previous section.
Here is an example of casting a named collection. If we have created the color_models table based on a VARRAY type as follows:
CREATE TYPE Color_array_t AS VARRAY(16) OF VARCHAR2(30); CREATE TABLE color_models_a ( model_type VARCHAR2(12), colors Color_array_t);
we can CAST the VARRAY colors column as a nested table and apply the THE pseudo-function to the result:
SELECT COLUMN_VALUE FROM THE(SELECT
CAST(colors AS Color_tab_t)
FROM color_models_a WHERE model_type = 'FGB');
CAST performs an on-the-fly conversion of the Color_array_t collection type to the Color_tab_t collection type.
NOTE: CAST is available only within SQL statements. That means you can't use it directly within PL/SQL. However, it is useful in SELECTs and DML statements that appear in your PL/SQL programs.
A subtle difference exists between what you can accomplish with CAST and what you can accomplish with THE. As we saw in the previous section, THE can serve on either the "left-hand side" or the "right-hand side" of an INSERT, UPDATE, or DELETE statement. THE subqueries can be part of the source, or the target, of these DML statements. By contrast, CAST only works in SELECTs or on the right-hand side of DML statements.
It is also possible to cast a "bunch of records" -- such as the result of a subquery -- as a particular collection type. Doing so requires the MULTISET function, covered in the next section.
This new Oracle8 function exists only for use within CASTs. MULTISET allows you to retrieve a set of data and convert it on-the-fly to a collection type. The simplest example is this:
SELECT CAST (MULTISET (SELECT field FROM table ) AS collection-type ) FROM DUAL;
So if we happened to have a relational table of colors:
CREATE TABLE some_colors ( color_name VARCHAR2(30), color_classification VARCHAR2(30));
and we wanted to CAST to a collection so we could fetch a set of them at once, we could do this:
DECLARE some_hot_colors Color_tab_t; BEGIN SELECT CAST(MULTISET(SELECT color_name FROM some_colors WHERE color_classification = 'HOT') AS Color_tab_t) INTO some_hot_colors FROM DUAL; END;
Another way to use MULTISET involves a correlated subquery in the SELECT list:
SELECT
outerfield
, CAST(MULTISET(SELECT
field
FROM
whateverTable
WHERE
correlationCriteria
) AS
collectionTypeName
) FROM
outerTable
;
This technique is useful for making joins look as if they include a collection. Going back to our birds example, suppose that we had a detail table that listed, for each bird, the countries where that species lives:
CREATE TABLE birds ( genus VARCHAR2(128), species VARCHAR2(128), colors Color_array_t, PRIMARY KEY (genus, species) ); CREATE TABLE bird_habitats ( genus VARCHAR2(128), species VARCHAR2(128), country VARCHAR2(60), FOREIGN KEY (genus, species) REFERENCES birds (genus, species) ); CREATE TYPE Country_tab_t AS TABLE OF VARCHAR2(60);
We should then be able to "smush" the master and detail tables together in a single SELECT that converts the detail records into a collection type. This feature has enormous significance for client/server programs, since the number of round trips can be cut down without the overhead of duplicating the master records with each and every detail record:
DECLARE CURSOR bird_curs IS SELECT b.genus, b.species, CAST(MULTISET(SELECT bh.country FROM bird_habitats bh WHERE bh.genus = b.genus AND bh.species = b.species) AS country_tab_t) FROM birds b; bird_row bird_curs%ROWTYPE; BEGIN OPEN bird_curs; FETCH bird_curs into bird_row; CLOSE bird_curs; END;
NOTE: In Oracle 8.0.3 this code produces the error "PLS-00201: identifier 'B.GENUS' must be declared." It works properly in 8.0.4. See Oracle's SQL Reference Guide for more information and examples. As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.
The workaround for this bug is to create a packaged function which will return the desired data expressed in the form of a collection. Then you can use this function, rather than the correlated subquery, in the SELECT statement. Here is the package specification that we need:
CREATE PACKAGE fixbug AS FUNCTION get_country_tab (p_genus IN VARCHAR2, p_species IN VARCHAR2) RETURN Country_tab_t; PRAGMA RESTRICT_REFERENCES (get_country_tab, WNDS, RNPS, WNPS); END;
And the body:
CREATE PACKAGE BODY fixbug AS FUNCTION get_country_tab (p_genus IN VARCHAR2,p_species IN VARCHAR2) RETURN Country_tab_t IS l_country_tab Country_tab_t; CURSOR ccur IS SELECT CAST(MULTISET(SELECT bh.country FROM bird_habitats bh WHERE bh.species = p_species AND bh.genus = p_genus) AS country_tab_t) FROM DUAL; BEGIN OPEN ccur; FETCH ccur INTO l_country_tab; CLOSE ccur; RETURN l_country_tab; END; END;
Note that the CAST above uses a selection from DUAL to substitute for the correlated subquery.
We can now rewrite our original PL/SQL fragment as follows:
DECLARE CURSOR bird_curs IS SELECT b.genus, b.species,
fixbug.get_country_tab(b.genus, b.species)
FROM birds b; bird_row bird_curs%ROWTYPE; BEGIN OPEN bird_curs; FETCH bird_curs into bird_row; CLOSE bird_curs; END;
Just what you were hoping for, another use of the TABLE keyword! In this case, TABLE is operating as a function that coerces a collection-valued column into something you can SELECT from. It sounds complicated, but this section presents an example that's not too hard to follow.
Looking at it another way, let's say that you have a database table with a column of a collection type. How can you figure out which rows in the table contain a collection that meets certain criteria? That is, how can you select from the database table, putting a WHERE clause on the collection's contents?
Wouldn't it be nice if you could just say:
SELECT * FROM table_name WHERE collection_column HAS CONTENTS 'whatever'; -- invalid; imaginary syntax!
Logically, that's exactly what you can do with the TABLE function. Going back to our color_models database table, how could we get a listing of all color models which contain the color RED? Here's the real way to do it:
SELECT * FROM color_models c WHERE 'RED' IN (SELECT * FROM TABLE(c.colors));
which, in SQL*Plus, returns
MODEL_TYPE COLORS ------------ ------------------------------------------------------ RGB COLOR_TAB_T('RED', 'GREEN', 'BLUE')
The query means "go through the color_models table and return all rows whose list of colors contains at least one RED element." Had there been more rows with a RED element in their colors column, these rows too would have appeared in our SQL*Plus result set.
As illustrated above, TABLE accepts an alias-qualified collection column as its argument:
TABLE(alias_name.collection_name)
TABLE returns the contents of this collection coerced into a virtual database table. Hence, you can SELECT from it; in our example, it's used in a subquery.
Does the TABLE function remind you vaguely of the THE pseudo-function? Recall our THE example:
SELECT VALUE(c) FROM THE(SELECT colors FROM color_models WHERE model_type = 'RGB') c;
which (in Oracle 8.0.4 and later) returns:
VALUE(C) ------------------------------ RED GREEN BLUE
So what is the difference between the pseudo-functions THE and TABLE? Both return something that, for purposes of the rest of the SQL statement, serves as a "virtual database table." So the difference between the functions must lie in that on which they operate -- their "inputs." The TABLE function operates on a (column-typed) nested table. By contrast, the pseudo-function THE operates on a SELECT statement's result set that contains exactly one row with one column which is a (column-typed) nested table.
As it turns out, the TABLE function gets called "under the covers" whenever you use THE as the target of an INSERT, UPDATE, or DELETE statement. This under-the-covers call coerces the results of the subquery into a virtual database table upon which the DML makes sense to operate.
TIP: To repeat an earlier admonition, none of the collection pseudo-functions are available from within PL/SQL, but PL/SQL programmers will certainly want to know how to use these gizmos in their SQL statements!
Personally, I find these new features fascinating, and I enjoy the mental calisthenics required to understand and use them. Maybe mine isn't a universal sentiment, but at least you can admit that Oracle hasn't let their language technology get tired!
|
|
|
|
| 19.4 Using Collections |
|
19.6 Collection Built-Ins |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.