PL/SQL Record

Summary: in this tutorial, you will learn about the PL/SQL record and how to use records to manage your program data effectively.

Overview of PL/SQL Record

A PL/SQL record is a composite data structure that consists of multiple fields; each has its own value. The following picture shows an example record that includes first name, last name, email, and phone number:

PL/SQL record helps you simplify your code by shifting from field-level to record-level operations.

PL/SQL has three types of records: table-based, cursor-based, and programmer-defined. Before using a record, you must declare it.

Declaring records

You define and declare records in the declaration section of a block or via package specification.

Table-based record

To declare a table-based record, you use the %ROWTYPE attribute with a table name. A table-based record has each field corresponding to a column in a table.

DECLARE
   record_name table_name%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example declares a record named r_contact with the same structure as the contacts table in the sample database:

DECLARE
   r_contact contacts%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Cursor-based record

A cursor-based record has each field corresponding to a column or alias in the cursor SELECT statement.

To declare a cursor-based record, you use the %ROWTYPE attribute with an explicit cursor as shown below:

DECLARE
    record_name cursor_name%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example declares a record with the same structure as an explicit cursor:

DECLARE
    CURSOR c_contacts IS
        SELECT first_name, last_name, phone
        FROM contacts;
    r_contact c_contacts%ROWTYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

First, declare an explicit cursor that fetches data from the first_name, last_name, and phone columns of the contacts table.
Second, declare a record named r_contact whose structure is the same as the c_contacts cursor.

Programmer-defined record

The table-based and cursor-based records are good enough when you need to create records based on existing structures.

If you want to create a record whose structure is not based on the existing ones, then you use a programmer-defined record.

To declare a programmer-defined record, you use the following steps:

Define a record type that contains the structure you want in your record.
Declare a record based on the record type.

The following shows the syntax for defining a record type:

TYPE record_type IS RECORD (
    field_name1 data_type1 [[NOT NULL] := | DEFAULT default_value],
    field_name2 data_type2 [[NOT NULL] := | DEFAULT default_value],
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To declare a record based on the predefined record type, you use the following syntax:

record_name record_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example defines a record type whose name is customer_contacts and a record whose type is customer_contacts:

DECLARE
  -- define a record type
TYPE r_customer_contact_t
IS
  RECORD
  (
    customer_name customers.name%TYPE,
    first_name    contacts.first_name%TYPE,
    last_name     contacts.last_name%TYPE );
  -- declare a record
  r_customer_contacts r_customer_contact_t;
BEGIN
  NULL;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Referencing a record’s field

You reference a field in a record via the dot notation as shown below:

record_name.field_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, to reference the first_name field of the r_contact record, you use the following expression:

r_contact.first_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Assigning records

You can assign a record to another record of the same type, for example:

r_contact1 := r_contact2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

But you cannot compare two records of the same type via a comparison operator. The following example is an invalid comparison:

IF r_contact1 = r_contact2 THEN
    ...    
END IF; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this case, you need to compare individual fields of the record instead:

IF r.contact1.first_name = r.contact2.first_name AND 
    r.contact1.last_name = r.contact2.last_name AND
    r.contact1.phone = r.contact2.phone THEN
    ...
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can assign a value to the individual field of a record, for example:

r_contact.first_name := 'John';
r_contact.last_name := 'Doe';
r_contact.phone := '(408-654-2865)';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can use SELECT INTO a whole record (or individual fields):

SELECT
  first_name, last_name, phone
INTO
  r_contact
FROM
  contacts
WHERE
  contact_id = 100;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can FETCH INTO a whole record or individual fields:

-- fetch a whole record
FETCH cur_contacts INTO r_contact;

-- fetch individual fields
FETCH
  cur_contacts
INTO
  r_contact.first_name, 
  r_contact.last_name, 
  r_contact.phone;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Records and INSERT / UPDATE statements

You can insert a new row into a table using a %ROWTYPE record without having to specify each field. The following statement creates a new table named persons for demonstration:

CREATE TABLE persons  (
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2( 50 ) NOT NULL, 
    last_name VARCHAR2( 50 ) NOT NULL,
    primary key (person_id)
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following block inserts a new row into the persons table using a %ROWTYPE record:

DECLARE
  r_person persons%ROWTYPE;

BEGIN
  -- assign values to person record
  r_person.person_id  := 1;
  r_person.first_name := 'John';
  r_person.last_name  := 'Doe';

  -- insert a new person
  INSERT INTO persons VALUES r_person;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To update a row from a %ROWTYPE record, you use the SET ROW keywords as shown in the following example:

DECLARE
  r_person persons%ROWTYPE;

BEGIN
  -- get person data of person id 1
  SELECT * INTO r_person 
  FROM persons 
  WHERE person_id = 1;

  -- change the person's last name
  r_person.last_name  := 'Smith';

  -- update the person
  UPDATE persons
  SET ROW = r_person
  WHERE person_id = r_person.person_id;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Nested record

A record can contain a field which is another record. Nesting records are a powerful way to structure your program data and hide complexity in your code.

The following example declares a record type named address. Then the record type customer has two fields ship_to and bill_to which are based on the address record type.

DECLARE
  TYPE address IS RECORD (
    street_name VARCHAR2(255),
    city VARCHAR2(100),
    state VARCHAR2(100),
    postal_code VARCHAR(10),
    country VARCHAR2(100)
  );
  TYPE customer IS RECORD(
      customer_name VARCHAR2(100),
      ship_to address,
      bill_to address
  );
  r_one_time_customer customer;
BEGIN

  r_one_time_customer.customer_name := 'John Doe';
  -- assign address
  r_one_time_customer.ship_to.street_name := '4000 North 1st street';
  r_one_time_customer.ship_to.city := 'San Jose';
  r_one_time_customer.ship_to.state := 'CA';
  r_one_time_customer.ship_to.postal_code := '95134';
  r_one_time_customer.ship_to.country := 'USA';
  -- bill-to address is same as ship-to address
  r_one_time_customer.bill_to := one_time_customer.ship_to;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this tutorial, you have learned about PL/SQL records and how to use them effectively to simplify your code.

Was this tutorial helpful?