Table 12.2. Character datatype maximum lengths and database column widths.
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
The two datatypes in the number datatype category are BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of _2 31 to 2 31 _1. The most common use for this datatype is an index for PL/SQL tables.
Storage for fixed or floating-point numbers of any
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.
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
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.
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
Arrays are available as information stores
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.
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
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;
You can use one of three
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;
You cannot assign records of different types to each other.
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.
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.
Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL
<<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.