December 2002
Intermediate to advanced
928 pages
85h 29m
English
You can manipulate collections with the following functions:
Maps a collection of one type to a collection of another type.
SELECT column_value
FROM TABLE(SELECT CAST(colors AS color_tab_t)
FROM color_models_a
WHERE model_type ='RGB');Maps a database table to a collection. With MULTISET and CAST, you can retrieve rows from a database table as a collection-typed column. You can use CAST and MULTISET to extract nested columns for use in trigger logic (described later in the chapter.)
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;Maps a collection to a database table; the inverse of MULTISET.
SELECT * FROM color_models c WHERE 'RED' IN (SELECT * FROM TABLE(c.colors));
You can use TABLE( ) to unnest a transient collection:
DECLARE
birthdays Birthdate_t :=
Birthdate_t('24-SEP-1984', '19-JUN-1993');
BEGIN
FOR the_rec IN
(SELECT COLUMN_VALUE
FROM TABLE(CAST(birthdays AS Birthdate_t)))Collections also support a set of methods, described in the “Collection Datatypes” section in Appendix A. These methods are:
| COUNT |
| DELETE |
| EXISTS |
| EXTEND |
| FIRST |
| LAST |
| LIMIT |
| PRIOR |
| NEXT |
| TRIM |
The syntax for using these methods is:
collection_name.method_name[(parameters)]