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.