O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 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),
  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
     FROM books
    WHERE title = 'Oracle PL/SQL Programming, 5th Edition';

   IF my_book.author LIKE '%Feuerstein%'
   THEN
      DBMS_OUTPUT.put_line ('Our newest ISBN is ' || my_book.isbn);
   END IF;
END;

I can also define my own record type and use that as the basis for declaring records. Suppose, for example, that I want to work only with the author and title of a book. Rather than use %ROWTYPE to declare my record, I will instead create a record type:

DECLARE
   TYPE author_title_rt IS RECORD (
      author books.author%TYPE
     ,title books.title%TYPE
     );
   l_book_info author_title_rt;
BEGIN
   SELECT author, title INTO l_book_info
     FROM books WHERE isbn = '0-596-00977-1';

Let’s take ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required