Oracle Database 10g SQL (Osborne ORACLE Press Series)

In this chapter, you will

• Be introduced to collections

• Learn how to create collection types

• Use collection types to define columns in tables

• Perform DML operations with collections

• Learn how a collection type may contain elements that are also collection types

• Use collections in PL/SQL

• Examine Oracle10 g enhancements to collections

Introducing Collections

The Oracle8 database introduced two new database types known as collections , which allow you to store sets of elements. The Oracle9 i database extends these features to include multilevel collections, which allow you to create a collection that is itself a collection. The Oracle10 g database further improves collections to include more ANSI functionality along with other enhancements.

There are three types of collections:

• Varrays    A varray is similar to an array in Java. You can use a varray to store an ordered set of elements with each element having an index associated with it. The elements in a varray are of the same type, and a varray has one dimension. A varray has a maximum size that you set when creating it, but you can change the size later.

• Nested tables    A nested table is a table that is embedded within another table. You can insert, update, and delete individual elements in a nested table. Because you can modify individual elements in a nested table, this makes them more flexible than a varray because elements in a varray can only be modified as a whole, not individually. A nested table doesn t have a maximum size, and you can store an arbitrary number of elements in a nested table.

• Associative arrays (formerly known as index-by tables)    New for Oracle10 g , an associative array is a set of key and value pairs. You can get the value from the array using the key (which may be a string) or an integer that specifies the position of the value in the array. An associative array is similar to a hash table in programming languages such as Java. You ll learn how to use associative arrays later in the section Associative Arrays.

You create a collection type using the SQL DDL CREATE TYPE statement, and you then use these types to define columns in a table. The elements stored in a varray are stored with the table when the size of the varray is 4KB or less, otherwise the varray is stored outside of the table. The elements for nested tables are stored in separate tables. When a varray is stored with the table, accessing its elements is faster than accessing elements in a nested table. An associative array is a PL/SQL construct, not a SQL construct. An associative array cannot be stored persistently in a table.

You might be asking yourself why you would want to use collections in the first place. After all, using two tables with a foreign key already allows you to model relationships between data. The answer is that the data stored in the collection may be accessed more rapidly by the database than if you were to use two tables instead. Typically, you ll want to use a collection if you have data that is only used by one table. For example, this chapter uses collections to store addresses for customers, and those addresses are used only within the tables they are stored in.