Oracle PL/SQL for DBAs

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 to—and has the same name as—a 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.