PL/SQL Records – Structure, Examples & Usage

PL/SQL Records allow grouping of variables into a single composite data structure, making it easier to work with rows of a database table. This is especially useful when handling data that mirrors the structure of relational tables. In this guide, you’ll learn how PL/SQL records work, how to declare and use them, and view practical examples of their use.

What Is a PL/SQL Record?

A PL/SQL record is a data structure that can hold multiple values of different data types, similar to a row in a database table. It is made up of fields, each with its own name and data type. Records are declared using the TYPE keyword and can be based on table rows or created independently.

example:

DECLARE
  TYPE emp_record_type IS RECORD (
    emp_id    NUMBER,
    emp_name  VARCHAR2(100),
    salary    NUMBER
  );
  emp_rec emp_record_type;
BEGIN
  emp_rec.emp_id := 101;
  emp_rec.emp_name := 'John';
  emp_rec.salary := 50000;
  DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.emp_name);
END;

You can create a record that directly maps to a table row using %ROWTYPE:

DECLARE
  emp_row employees%ROWTYPE;
BEGIN
  SELECT * INTO emp_row FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(emp_row.first_name || ' ' || emp_row.last_name);
END;

Working with Records

To use records efficiently:

Declare a record type or use %ROWTYPE
Declare a variable of that record type
Assign values using SQL queries or directly in code
Access fields using dot notation (record_name.field_name)

PL/SQL Records vs Object Types

Feature	PL/SQL Records	Object Types
Syntax	Simpler	More complex
Use case	Local code structures	Reusable, stored
Constructor	Not required	Required (Oracle 10g+)
Method support	No	Yes

Records are typically used for local variable grouping and simple data manipulation within PL/SQL blocks.

Use Case Example: Fetching Data into a Record

DECLARE TYPE
  book_record_type IS RECORD (
    isbn   BOOKS.ISBN%TYPE,
    title  BOOKS.TITLE%TYPE,
    price  BOOKS.PRICE%TYPE
  );
  book_rec book_record_type;
BEGIN
  SELECT isbn, title, price INTO book_rec FROM books WHERE isbn = '12345678';
  DBMS_OUTPUT.PUT_LINE('Book: ' || book_rec.title || ' - Price: ' || book_rec.price);
END;

Summary

PL/SQL Records simplify working with complex data by allowing multiple fields of various data types to be grouped together. They’re ideal for manipulating table rows in memory, especially when used with cursors or procedural logic.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.