Composite Data Types

The two composite data types in PL/SQL are TABLE and RECORD. The TABLE data type enables the user to define a PL/SQL table to be used for array processing. The RECORD data type enables the user to go beyond the %ROWTYPE variable attribute; with it, you specify user-defined fields and field data types.
Array Processing

The TABLE composite data type provides the developer a mechanism for array processing. Although it's limited to one column of information per PL/SQL table, you can store any number of rows for that column. The word from Oracle is that future versions of PL/SQL will provide more flexibility in the use of tables.

In the order_total example, define a PL/SQL table named g_recip_list (the information will be used globally).
The following is an illustration of this concept:

TYPE RecipientTabTyp IS TABLE OF NUMBER(22)

INDEX BY BINARY_INTEGER;

...

g_recip_list RecipientTabTyp;

To initialize an array, you must first define an array name or TYPE, which in this example is RecipientTabTyp. This TABLE column is defined as NUMBER with a maximum of 22 digits. You can define the column as any valid PL/SQL data type; however, the primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make reference for variable definition as shown with g_recip_list defined as an array of TYPE RecipientTabTyp.
Building Arrays

Arrays are available as information stores subsequent to initialization of the array. To store information in the array g_recip_list that was defined in the last example, you simply reference the array with a numeric value. This is shown in the following example:

g_recip_list(j) := g_recipient_num(i)

In this example, i and j are counters with values 1. . .n. Once information is stored in an array, you can access it, also with numeric values, as shown in the example. In this case, rows of g_recipient_num are referenced for storage in g_recip_list.


Record Processing

The RECORD composite data type provides the developer a mechanism for record processing as described previously. Although you cannot initialize TABLEs at the time of declaration, you can with RECORDs, as illustrated in the following example:

TYPE LineRecTyp IS RECORD

(merch_gross NUMBER := 0,

recip_num NUMBER := 0 );

...

li_info LineRecTyp;

Defining a RECORD of TYPE LineRecTyp allows declarations such as li_info of that TYPE as shown. You can use this method of RECORD declaration in place of the li_info declaration in the previous %ROWTYPE example. As with %ROWTYPE, references to RECORD information is accomplished with dot notation.

g_order_merch_total := g_order_merch_total + li_info.merch_gross;

You can use one of three methods to assign values to records. First, you can assign a value to a record field as you would assign any variable.

li_info.merch_gross := 10.50;

A second method is to assign all fields at once by using two records that are declared with the same data type. Assume a second LineRecTyp is defined as new_li_info.

new_li_info := li_info;

This statement assigns all fields of new_li_info the values from the same fields of li_info.

A third method of assigning values to fields of a record is through SQL SELECT or FETCH statements.

OPEN c_line_item;

...

FETCH c_line_item

INTO li_info;

In this case, all fields of li_info are assigned values from the information retrieved by the FETCH of cursor c_line_item.