Oracle PL/SQL Tutorial/Collections/Introduction

Collections stores sets of elements

There are three types of collections:

Varrays,Nested tables,Associative arrays (formerly known as index-by tables)

A varray is similar to an array in Java.

You can use a varray to store an ordered set of elements having an index associated with it.

The elements in a varray are of the same type.

A varray has one dimension.

A varray has a maximum size that you set when creating it.

Elements in a varray can only be modified as a whole, not individually.

You can change the size of a varray later.

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.

When a varray is stored with the table, accessing its elements is faster than accessing elements in a nested table.

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.

A nested table doesn"t have a maximum size, and you can store an arbitrary number of elements in a nested table.

The elements for nested tables are stored in separate tables.

Associative arrays is formerly known as index-by tables.

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.

An associative array is similar to a hash table.

You create a collection type using the SQL DDL CREATE TYPE statement.

Then you can use these types to define columns in a 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.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

26. 1. Introduction 26. 1. 1. Collections stores sets of elements 26. 1. 2. <A href="/Tutorial/Oracle/0520__Collections/MultilevelCollectionTypes.htm">Multilevel Collection Types</a> 26. 1. 3. <A href="/Tutorial/Oracle/0520__Collections/ListofCollectionmethods.htm">List of Collection methods</a>

List of Collection methods

A series of collection methods can be used to determine the size, and the rows populated, in any collection datatype: index-by tables, VARRAYs, and nested tables. The following is a list of the collection methods and their purposes:

1. EXISTS(row) returns TRUE if the row specified exists.
2. COUNT returns the number of rows.
3. FIRST returns the row number of the first populated row.
4. LAST returns the row number of the last populated row.
5. PRIOR(row) returns the row number of the last row populated before the row specified.
6. NEXT(row) returns the row number of the next row populated after the row specified.
7. DELETE removes all rows.
8. DELETE(row) removes the specified row.
9. DELETE(start_row,end_row) removes all rows between and including the start_row and end_row.
10. TRIM removes the last row.
11. TRIM(n) removes the last n rows.
12. EXTEND adds one row.
13. EXTEND(n) adds n rows.
14. EXTEND(n,m) adds n copies of row m.

26. 1. Introduction 26. 1. 1. <A href="/Tutorial/Oracle/0520__Collections/Collectionsstoressetsofelements.htm">Collections stores sets of elements</a> 26. 1. 2. <A href="/Tutorial/Oracle/0520__Collections/MultilevelCollectionTypes.htm">Multilevel Collection Types</a> 26. 1. 3. List of Collection methods

Multilevel Collection Types

A collection type whose elements are also a collection type is known as a multilevel collection type.

The following list shows the valid multilevel collection types:

1. A nested table containing a nested table type
2. A nested table containing a varray type
3. A varray containing a varray type
4. A varray containing a nested table type
5. A varray or nested table of an object type that has an attribute that is a varray or nested table type

26. 1. Introduction 26. 1. 1. <A href="/Tutorial/Oracle/0520__Collections/Collectionsstoressetsofelements.htm">Collections stores sets of elements</a> 26. 1. 2. Multilevel Collection Types 26. 1. 3. <A href="/Tutorial/Oracle/0520__Collections/ListofCollectionmethods.htm">List of Collection methods</a>