Collections

Collections are programming structures that hold sets of like things. Collections fall into two categories: arrays and lists. Arrays typically have a physical size allocated when you define them, while lists have no physical limit imposed. These lists are often indexed by a series of sequential numbers that start with 0 or 1 and increase one value at a time.  Lists can also 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.The VARRAY and NESTED TABLE collections can be defined as both SQL and PL/SQL datatypes while Associative Array (previously known as PL/SQL Table) is only a PL/SQL datatype. Numeric indexes for associative arrays do not need to be sequential and are non-sequential structures. Non-sequential structures can have gaps in index sequences and are known as sparsely populated structures. Associative arrays are dynamically sized and, like the NESTED TABLE datatype, have no fixed size.

When to use what?

1. Use a varray when the physical size of the collection is static and the collection may be used in tables. Varrays are the closest thing to arrays in other programming languages, such as Java, C, C++, or C#.

2. Use nested tables when the physical size is unknown due to run-time variations and when the type may be used in tables. Nested tables are like lists and bags in other programming languages.

3.Use associative arrays when the physical size is unknown due to run-time variations and when the type will not be used in tables. Associative arrays are ideal for standard programming solutions, such as using maps and sets.

VARRAY :

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

CREATE OR REPLACE TYPE type_name AS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

Varray example: http://wp.me/p1YiQQ-11

NESTED Table:

TYPE type_name IS TABLE OF element_type [ NOT NULL ];

CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [ NOT NULL ];

Nested table example :http://wp.me/p1YiQQ-15

Associative Arrays:

Associative arrays are single-dimensional structures of an Oracle 11g datatype or a user-defined
record/object type. Associative arrays cannot be used in tables. They may be used only as programming structures. They can be accessed only in PL/SQL.

1.

Do not require initialization and have no constructor syntax. They do not need to
allocate space before assigning values, which eliminates using the Collection API
EXTEND method.

2.

Can be indexed numerically up to and including Oracle 11g. In Oracle 11g, they can
also use unique variable-length strings.

3.

Can use any integer as the index value, which means any negative, positive, or zero
whole number.

4.

Are implicitly converted from equivalent %ROWTYPE, record type, and object type return
values to associative array structures.

5.

Are the key to using the FORALL statement or BULK COLLECT clause, which enables
bulk transfers of records from a database table to a programming unit.

6.

Require special treatment when using a character string as an index value in any
database using globalized settings, such as the NLS_COMP or NLS_SORT initialization
parameters.

Syntax for creating as Associative Array:

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type [ NOT NULL ]
INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];

Associative Array Example: http://wp.me/p1YiQQ-17

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s