Associative Array Example

DECLARE
–– Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
–– Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
–– Declare and construct a varray.
month MONTHS_VARRAY :=
months_varray(‘January’,’February’,’March’,’April’,’May’,’June’
,’July’,’August’,’September’,’October’,’November’,’December’);
–– Declare an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
–– Check if calendar has no elements, then add months.
IF calendar.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘–——––––––––’);
FOR i IN month.FIRST..month.LAST LOOP
calendar(i) := ”;
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
calendar(i) := month(i);

END LOOP;
–– Print assigned element values.
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE(‘Post-assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘––––––––––———–‘);
FOR i IN calendar.FIRST..calendar.LAST LOOP
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
END LOOP;
END IF;
END;

/

Advertisements

Nested Table Example

TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
— Declare a nested table with null values.
cards CARD_TABLE := card_table(NULL,NULL,NULL);
BEGIN
— Print initialized null values.
dbms_output.put_line(‘Nested table initialized as null values.’);
dbms_output.put_line(‘——————–––––––––––––––––––––’);
FOR i IN 1..3 LOOP
dbms_output.put (‘Cards Varray [‘||i||’] ‘);
dbms_output.put_line(‘[‘||cards(i)||’]’);
END LOOP;
–– Assign values to subscripted members of the nested table.

cards(1) := ‘Ace’;
cards(2) := ‘Two’;
cards(3) := ‘Three’;
–– Print initialized null values.
dbms_output.put (CHR(10)); — Visual line break.
dbms_output.put_line(‘Nested table initialized as 11, 12 and 13.’);
dbms_output.put_line(‘———————–––––––––––––––––––––’);
FOR i IN 1..3 LOOP
dbms_output.put_line(‘Cards [‘||i||’] ‘||'[‘||cards(i)||’]’);
END LOOP;
END;
/

Varray Example

DECLARE
–– Define a varray with a maximum of 3 rows.
TYPE integer_varray IS VARRAY(3) OF INTEGER;
–– Declare the varray with null values.
varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
— Print initialized null values.
dbms_output.put_line(‘Varray initialized as nulls.’);
dbms_output.put_line(‘––––––––––––––————–‘);
FOR i IN 1..3 LOOP

dbms_output.put (‘Integer Varray [‘||i||’] ‘);
dbms_output.put_line(‘[‘||varray_integer(i)||’]’);
END LOOP;
–– Assign values to subscripted members of the varray.
varray_integer(1) := 11;
varray_integer(2) := 12;
varray_integer(3) := 13;

— Print initialized null values.
dbms_output.put (CHR(10)); –- Visual line break.
dbms_output.put_line(‘Varray initialized as values.’);
dbms_output.put_line(‘––-––––––––––––————–‘);
FOR i IN 1..3 LOOP
dbms_output.put_line(‘Integer Varray [‘||i||’] ‘
|| ‘[‘||varray_integer(i)||’]’);
END LOOP;
END;
/

The syntax of defining varray as an object in database is:

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

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