Skip Headers

PL/SQL User's Guide and Reference
Release 2 (9.2)

Part Number A96624-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

5
PL/SQL Collections and Records

Knowledge is that area of ignorance that we arrange and classify. --Ambrose Bierce

Many programming techniques use collection types such as arrays, bags, lists, nested tables, sets, and trees. To support these techniques in database applications, PL/SQL provides the datatypes TABLE and VARRAY, which allow you to declare index-by tables, nested tables and variable-size arrays. In this chapter, you learn how those types let you reference and manipulate collections of data as whole objects. You also learn how the datatype RECORD lets you treat related but dissimilar data as a logical unit.

This chapter discusses the following topics:

"What Is a Collection?"
"Choosing Which PL/SQL Collection Types to Use"
"Defining Collection Types"
"Declaring PL/SQL Collection Variables"
"Initializing and Referencing Collections"
"Assigning Collections"
"Using PL/SQL Collections with SQL Statements"
"Using Collection Methods"
"Avoiding Collection Exceptions"
"Reducing Loop Overhead for Collections with Bulk Binds"
"What Is a Record?"
"Defining and Declaring Records"
"Initializing Records"
"Assigning Records"
"Manipulating Records"

What Is a Collection?

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

PL/SQL offers these collection types:

Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

Understanding Nested Tables

Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

PL/SQL nested tables are like one-dimensional arrays. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.

Nested tables differ from arrays in two important ways:

  1. Arrays have a fixed upper bound, but nested tables are unbounded (see Figure 5-1). So, the size of a nested table can increase dynamically.

Figure 5-1 Array versus Nested Table

Text description of pls81016_array_versus_nested_table.gif follows
Text description of the illustration pls81016_array_versus_nested_table.gif



Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback