PL/SQL Record

A record is a composite datatype that groups related values into a single logical unit. Each value in a record is stored in a field (also called a component), and each field has its own name and datatype. If you think of a database row as “one customer,” a PL/SQL record is a way to represent that same idea inside PL/SQL—so your code can move, validate, and transform related values together.

Records are useful because they reduce “parameter clutter” and make code more readable. Instead of passing six separate variables (name, address, city, state, zip, phone), you can pass a single record variable that contains all of them.

1. PL/SQL records are similar in spirit to structs/records in other programming languages.
2. A PL/SQL record is not automatically a database row, but it can be shaped to match a row.
3. Records treat multiple fields as a single unit of work, which improves correctness and maintainability.
4. Records are convenient for fetching and processing query results.
5. Record fields can be scalar types, other records, or collection types (including associative arrays).

You declare a user-defined PL/SQL record in two steps:

1. Define a record type (the “shape” of the record).
2. Declare a record variable of that type (the actual instance you use in code).

You can declare the record type in the declarative part of any PL/SQL block, subprogram, or package.

Field declarations work like variable declarations. Each field has a unique name and datatype. You can apply NOT NULL to a field, but then you must initialize that field (either by assigning a default value in the type declaration or by ensuring it is assigned before use).

Modern best practice: When a record is meant to represent data from a table, anchor field types with %TYPE (or use %ROWTYPE) so your PL/SQL stays aligned if table definitions change.

TYPE type_name IS RECORD (
  field_name1  field_type                 [NOT NULL],
  field_name2  variable%TYPE              [NOT NULL],
  field_name3  table_name.column_name%TYPE,
  field_name4  table_name%ROWTYPE
);

DECLARE
  TYPE ProductRecTyp IS RECORD (
    pname      VARCHAR2(30),
    sale_price NUMBER(10,2)
  );

  product_rec ProductRecTyp;
BEGIN
  SELECT product_name, sale_price
  INTO   product_rec.pname, product_rec.sale_price
  FROM   product
  WHERE  product_id = 2;
END;

Records often appear alongside other PL/SQL features you will use constantly:

• %TYPE anchoring: If product.product_name changes from VARCHAR2(30) to VARCHAR2(60), anchored record fields automatically stay correct.
• %ROWTYPE records: When you need a record shaped like an entire table row (or cursor row), table_name%ROWTYPE gives you the complete structure without listing every field.
• Cursor integration: Cursor FOR loops can fetch rows into implicit records, which is ideal when you iterate and transform row-by-row.
• Record parameters: Procedures/functions often accept a record parameter so callers pass one structured object instead of many scalars.

In Oracle 23ai-era development, these patterns remain central because they help you build PL/SQL that is durable across schema evolution and easier to validate, test, and refactor.
In the next lesson, you will learn about %TYPE attributes.