RelationalDBDesignRelationalDBDesign


PL/SQL   «Prev 

Querying Varray Syntax[varray, nested table, associative array]

SELECT <varray name>
FROM table_name
WHERE <condition for selection>;

Location 1 The SELECT clause containing a varray column
Location 2 The FROM clause containing the table name for the object table
Location 3 The WHERE clause to select specific records

Collections

There are three types of collections in the Oracle Database 11g family of products. They are the
  1. varray,
  2. nested table, and
  3. associative array datatypes.
Collections are powerful structures because they enable you to develop programs that manage large sets of data in memory.
You can build collections of any SQL or PL/SQL datatype. Collections of SQL datatypes work in both SQL and PL/SQL environments but collections of PL/SQL datatypes do not. They only work in PL/SQL. There is also some coverage of using collections as database columns. It covers these topics:
  1. Collection types
    1. Varrays
    2. Nested tables
    3. Associative arrays
  2. Collection set operators
  3. Collection API
Collections are programming structures that hold sets of like things. Collections fall into two categories: 1) arrays and 2) lists. Arrays typically have a physical size allocated when you define them, while lists have no physical limit imposed. Naturally, the memory available for processing in the SGA curtails the maximum size of some very large lists. These lists are often indexed by a series of sequential numbers that start with 0 or 1 and increase one value at a time. Using sequential numeric index values ensures that you can use the index to traverse a complete list by incrementing or decrementing one at a time in a loop. Alternatively, lists can be indexed by non-sequential numbers or unique strings. Lists are called associative arrays when they can be indexed by non-sequential numbers or unique strings. Figure 4-4 illustrates a collection of strings as an inverted tree, which represents a singledimensional collection. It uses a sequentially numbered index and would work with any SQL datatype or PL/SQL scalar or user-defined object type. The caveat on PL/SQL datatypes is that they can only be used in the context of PL/SQL blocks.

FIGURE 4-4: An inverted tree diagram of a single-dimensional SQL datatype collection