Oracle PL/SQL Programming, 6th Edition

Chapter 11. Records

A record is a composite data structure, which means that it is composed of more than one element or component, each with its own value. Records in PL/SQL programs are very similar in concept and structure to the rows of a database table. The record as a whole does not have a value of its own; instead, each individual component or field has a value, and the record gives you a way to store and access these values as a group. Records can greatly simplify your life as a programmer, allowing you to write and manage your code more efficiently by shifting from field-level declarations and manipulation to record-level operations.

Records in PL/SQL

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 following block 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),
  summary         VARCHAR2(2000),
  author          VARCHAR2(200),
  date_published  DATE,
  page_count      NUMBER
);

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 ...