0269-0271

From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the column width is much greater.

Number

The two datatypes in the number datatype category are BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of _2 ³¹ to 2 ³¹ _1. The most common use for this datatype is an index for PL/SQL tables.

Storage for fixed or floating-point numbers of any size is available using the NUMBER datatype. For floating-point numbers , you can specify precision and scale in the following format:

NUMBER(10,2)

A variable declared in this manner has a maximum of 10 digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38, whereas the scale range is _84 to 127.

Composite Datatypes

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

Array Processing

The TABLE composite datatype provides the developer with 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.

Page 270

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 datatype; however, the primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make a 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:

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 datatype provides the developer a mechanism for record processing, as described previously. Although you cannot initialize TABLEs at the time of declaration, you can initialize 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 are done with dot notation:

g_order_merch_total := g_order_merch_total + li_info.merch_gross;

Page 271

You can use one of three methods to assign values to records. First, you can assign a value to a record field as you 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 datatype. 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.

Processing Control

Every procedural language has control structures that provide processing of information in a logical manner by controlling the flow of information. Available structures within PL/SQL include IF-THEN-ELSE, LOOP, and EXIT-WHEN. These structures provide flexibility in manipulating database information.

Loop Control

Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example:

<<loop_name>>
LOOP
  (repetitive processing)
END LOOP loop_name;

To break out of a loop such as this, you must issue an EXIT or a GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. The following sections examine three types of PL/SQL loops that expressly define LOOP termination conditions.