[Chapter 9] 9.2 Table-Based Records

9.2 Table-Based Records

A table-based record, or table record, is a record whose structure (set of columns) is drawn from the structure (list of columns) of a table. Each field in the record corresponds to and has the same name as a column in the table. The fact that a table record always reflects the current structure of a table makes it useful when managing information stored in that table.

Suppose we have a table defined as the following:

CREATE TABLE rain_forest_history
   (country_code      NUMBER (5),
    analysis_date     DATE,
    size_in_acres     NUMBER,
    species_lost      NUMBER
   );

Like this table, a record created from it would also have four fields of the same names. You must use dot notation to reference a specific field in a record. If the record for the above table were named rain_forest_rec, then the fields would each be referred to as:

rain_forest_rec.country_code
rain_forest_rec.analysis_date
rain_forest_rec.size_in_acres
rain_forest_rec.species_lost

9.2.1 Declaring Records with the %ROWTYPE Attribute

To create a table record, you declare it with the %ROWTYPE attribute. The %ROWTYPE attribute is very similar to the %TYPE attribute discussed in Chapter 4, Variables and Program Data, except that it is used to declare a composite structure rather than the simple, scalar variable produced with %TYPE. Sounds perfect for a record, doesn't it?

The general format of the %ROWTYPE declaration for a table record is:

<record_name> <table_name>%ROWTYPE;

where <record_name> is the name of the record, and <table_name> is the name of a table or view whose structure forms the basis for the record. Just as the %TYPE attribute automatically provides the column's datatype to the variable, %ROWTYPE provides the datatypes of each of the columns in a table for the record's fields.

In the following example, a %TYPE declaration defines a variable for the company name, while the %ROWTYPE declaration defines a record for the entire company row. A SELECT statement then fills the comp_rec record with a row from the table.

DECLARE
   comp_name company.name%TYPE;
   comp_rec  company%ROWTYPE;
BEGIN
   SELECT * FROM company INTO comp_rec
    WHERE company_id = 1004;

Notice that I do not need to specify the names of company's columns in either the record declaration or the SELECT statement. I can keep the code very flexible with the table record. If the DBA adds a column to the table, changes the name of a column, or even removes a column, the preceding lines of code will not be affected at all. (You would, however, need to recompile your programs in order to pick up the change in data structure.)

Of course, if my program makes an explicit reference to a modified column, that code would probably have to be changed. With a strong reliance on data manipulation through records, however, you can keep such references to a minimum.


9.1 Record Basics		9.3 Cursor-Based Records