Section 1.7. Records

1.7. Records

Each row in a table has one or more columns of various datatypes. Similarly, a record is composed of one or more fields. There are three different ways to define a record, but once defined, the same rules apply for referencing and changing fields in a record.

The block below demonstrates the declaration of a record that is based directly on an underlying database table. Suppose that I have defined a table to keep track of my favorite books:

     CREATE TABLE books (       book_id         INTEGER,       isbn            VARCHAR2(13)       title           VARCHAR2(200),     );

I can then easily create a record based on this table, populate it with a query from the database, and then access the individual columns through the record's fields:

     DECLARE        my_book   books%ROWTYPE;     BEGIN        SELECT *          INTO my_book          FROM books         WHERE title = 'Oracle PL/SQL Programming, 4th Edition';     END;

1.7.1. Declaring Records

You can declare a record in one of three ways:

Table-based record

Use the %ROWTYPE attribute with a table name to declare a record in which each field corresponds toand has the same name asa column in a table. In the following example, I declare a record named one_book with the same structure as the books table:

     DECLARE        one_book books%ROWTYPE;

Cursor-based record

Use the %ROWTYPE with an explicit cursor or cursor variable in which each field corresponds to a column or aliased expression in the cursor SELECT statement. In the following example, I declare a record with the same structure as an explicit cursor:

 DECLARE    CURSOR my_books_cur IS       SELECT * FROM books        WHERE author LIKE '%FEUERSTEIN%';    one_SF_book my_books_cur%ROWTYPE;

Programmer-defined record

Use the TYPE RECORD statement to define a record in which each field is defined explicitly (with its name and datatype) in the TYPE statement for that record; a field in a programmer-defined record can even be another record. In the following example, I declare a record TYPE containing some information about my book-writing career and an "instance" of that TYPE, a record:

 DECLARE    TYPE book_info_rt IS RECORD (       author books.author%TYPE,       category VARCHAR2(100),       total_page_count POSITIVE);    steven_as_author book_info_rt;

Notice that when I declare a record based on a record TYPE, I do not use the %ROWTYPE attribute. The book_info_rt element already is a TYPE.

1.7.2. Working with Records

Regardless of how you define a record (based on a table, cursor, or explicit record TYPE statement), you work with the resulting record in the same ways. You can work with the data in a record at the record level, or you can work with individual fields of the record.

1.7.2.1. Record-level operations

When you work at the record level, you avoid any references to individual fields in the record. Here are the record-level operations currently supported by PL/SQL:

• You can copy the contents of one record to another (as long as they are compatible in structurethat is, have the same number of fields and the same or convertible datatypes).

• You can assign a value of NULL to a record with a simple assignment.

• You can define and pass the record as an argument in a parameter list.

• You can RETURN a record back through the interface of a function.

You can perform record-level operations on any records with compatible structures. In other words, the records must have the same number of fields and the same or convertible datatypes, but they don't have to be the same type. Suppose that I have created the following table:

     CREATE TABLE cust_sales_roundup (        customer_id NUMBER (5),        customer_name VARCHAR2 (100),        total_sales NUMBER (15,2)        );

Then the three records defined as follows all have compatible structures, and I can mix and match the data in these records as shown:

     DECLARE        cust_sales_roundup_rec cust_sales_roundup%ROWTYPE;        CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup;        cust_sales_rec cust_sales_cur%ROWTYPE;        TYPE customer_sales_rectype IS RECORD           (customer_id NUMBER(5),            customer_name customer.name%TYPE,            total_sales NUMBER(15,2)           );        prefererred_cust_rec customer_sales_rectype;     BEGIN        -- Assign one record to another.        cust_sales_roundup_rec := cust_sales_rec;        prefererred_cust_rec := cust_sales_rec;     END;

1.7.2.2. Field-level operations

When you need to access a field within a record (to either read or change its value), you must use dot notation , just as you would when identifying a column from a specific database table. The syntax for such a reference is:

     [schema_name.][package_name.]record_name.field_name

You need to provide a package name only if the record is defined in the specification of a package that is different from the one you are working on at that moment. You need to provide a schema name only if the package is owned by a schema different from that in which you are compiling your code.

Once you have used dot notation to identify a particular field, all the normal rules in PL/SQL apply as to how you can reference and change the value of that field. Let's take a look at some examples.