Page 205
This provides excellent control over data typing problems that occur when a database column or table changes. Because the variable's type is based, rather than explicitly declared, the correct data type and length are already in place. If the table changes, the code need only be recompiled. In this fashion, based variables provide a measure of data independence, minimizing code maintenance. Imagine how much work would be required to modify the code in perhaps dozens of modules when some column's length or type changed, had an explicit data type been used in the code instead. Of course, if the table column's data type changes from a CHAR(5) to a NUMBER(5), you'll still have some rework to do to your PL/SQL code (hey, it's happened to me), for example, inserting TO_CHAR() conversions.
When you use %TYPE, however, you get all the excess baggage. In the preceding example, you see that the programmer erroneously tried to assign a new value to a variable that is based on a constant variable, which makes the based variable a constant as well.
You should especially make use of based variables in subprogram parameter lists and function return declarations, where the types but not the lengths are declared.
Defining Composite Data TypesPL/SQL supports two kinds of composite (a vector, or set of values) data types: RECORD and TABLE. Composite variables can also be created by basing on a table or cursor row type, as above. These have similar semantics as RECORD variables.
To declare a new composite data type, you use the keyword TYPE. TYPE declarations only declare a new data type; they do not define storage. In order to use them, variables must be declared of that type. Here, the convention is to suffix the new data type's name with _TYPE to indicate its use as a data type.
DECLARE -- user-defined data types TYPE MY_STRING_TYPE IS RECORD OF ( str_len INTEGER := 0, -- initialized when variable is declared str VARCHAR2(32767)); -- defaults to NULL TYPE MY_ARRAY_TYPE IS TABLE OF CHAR(8) INDEX BY BINARY_INTEGER; -- variables str MY_STRING_TYPE; -- a new composite variable arr MY_ARRAY_TYPE; -- a new 1-D array
Creating Your Own Record TypesA RECORD type defines a structure that can contain any number of member variables of any data type, including previously defined RECORD or TABLE types. Like a record variable of some table %ROWTYPE, individual members are referenced using dot notation. Any initialization defined for a member occurs at runtime for the based variable.
You can nest RECORD types:
DECLARE ZIP_TYPE IS RECORD ( zip5 VARCHAR(5), DASH CONSTANT VARCHAR2(1) := `-', -- for display purposes perhaps plus4 VARCHAR2(4) := `0000'); -- initialized at runtime ADDR_TYPE IS RECORD ( line1 VARCHAR2(30), line2 VARCHAR2(30), city VARCHAR2(20), state VARCHAR2(2),
Page 206
zip_code ZIP_TYPE); EMPLOYEE_TYPE IS RECORD ( ssn VARCHAR2(9); dob DATE; address ADDR_TYPE); employee_rec EMPLOYEE_TYPE; -- actual storage defined BEGIN employee_rec.ssn := `123456789'; -- someone's SSN# employee_rec.address.city := `Pittsburgh'; employee_rec.address.zip_code.zip5 := `15210'; -- Pittsburgh's zip employee_rec.address.zip_code.plus4 := `3702'; -- Mt. Oliver in Pgh
Note the usage of dot notation to reference any sub-component of this nested record. The main saving grace of this form is that a lot of related data of differing data types can be managed more easily. A collection of data can be passed to or returned from a subroutine with a single parameter. It's tidier and more compact than the alternative. Records enable developers to think in terms of clumps of tightly related data.
You can assign one record variable to another only if they are declared to be of the same data type. Variables based on a database table ROWTYPE and those defined as a record type are always incompatible, even if the members match exactly.
DECLARE TYPE ORDERS_TYPE IS RECORD ( -- looks like the database table ord_num orders.ord_num%TYPE, quantity orders.quantity%TYPE, ... discount orders.discount%TYPE); orders_rec orders%ROWTYPE; -- based on a database table new_ords_rec ORDERS_TYPE; old_ords_rec ORDERS_TYPE; BEGIN ... -- do some work old_ords_rec := new_ords_rec; -- this is correct new_ords_rec := orders_rec; -- this is incorrect!
When inserting rows into the database table, the VALUES clause must specify each member separately.
INSERT INTO orders orders_rec; -- WRONG! INSERT INTO orders old_ords_rec; -- WRONG! INSERT INTO orders (ord_num, qty, ..., discount) VALUES (new_ords_rec.ord_num, new_ords_rec.quantity, , new_ords_rec.discount); -- CORRECT!
Creating Your Own ArraysA PL/SQL TABLE is a one-dimensional array of any single scalar type. For Oracle Servers before 7.3, TABLE cannot contain a RECORD or another TABLE. For Oracle 7.3 and up, a TABLE can contain a user-defined record, but not another TABLE.
A PL/SQL TABLE is not like an Oracle database table. It is an unbounded array whose elements do not exist until assigned a value. It is always indexed by BINARY_INTEGER, giving it elements whose indexes range ±2,147,483,647 (okay, not quite unbounded). The programmer can choose to begin indexing the array at offset 0, 1, or anywhere it makes sense. Because an unassigned element does not exist, the array can contain sparsely located values with no
Page 207
additional impact on memory usage. Each element is referenced with an offset in parentheses. You create a PL/SQL table variable by first declaring a table type, then basing a variable on the type. For example, the following code uses only enough memory to store these values:
DECLARE TYPE STR_TYPE IS TABLE OF VARCHAR2(8) INDEX BY BINARY INTEGER; arr STR_TYPE; i BINARY_INTEGER := 17; -- Universal Constant of Uncertainty BEGIN arr(-2,147,483,647) := `smallest'; arr(0) := `zero'; arr(+2,147,483,647) := `biggest'; arr(i) := `UCU'; -- an index variable makes for good loops
CAUTION |
A TABLE element is allocated a buffer sized to the data type and size for which it was declared, plus a little overhead. This means that if the table is of VARCHAR2(32767), each string stored in the table requires a 32KB buffer, even if the string is only one byte. Take care not to exhaust memory! |
To see what happens when you use VARCHAR2(32767) as the table's base type, run an operating system performance monitor showing allocated memory, then, on a non-critical system (such as a desktop instance), run Listing 10.10:
Listing 10.10 crash.sqlWARNING! This Program Will Drag the System to Its Knees!
DECLARE TYPE BIG_TYPE IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; big BIG_TYPE; BEGIN DBMS_OUTPUT.enable; FOR i IN 1..32767 LOOP big(i) := NULL; IF (MOD(i, 100) = 0) THEN -- display every 100 loops DBMS_OUTPUT.put_line(`Inserted NULL ` || TO_CHAR(i) || ` times'); END IF; -- displaying every 100 iterations END LOOP; END; /
Make sure to watch the operating system allocate all its memory, real and virtual. When available memory is used up, the program blows up. The following is an example of what you might get running Personal Oracle 7.2 on a Windows 95 machine:
Inserted NULL 100 times Inserted NULL 200 times Inserted NULL 300 times DECLARE * ERROR at line 1: