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 ...
Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.