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 which 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

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, 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Cursor-based record

A cursor-based record has each field corresponding 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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 programmer-defined record.

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

  1. Define a record type that contains the structure you want in your record.
  2. 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: SQL (Structured Query Language) (sql)

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

record_name record_type;
Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Referencing a record’s field

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

record_name.field_name
Code language: SQL (Structured Query Language) (sql)

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

r_contact.first_name
Code language: SQL (Structured Query Language) (sql)

Assigning records

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

r_contact1 := r_contact2;
Code language: SQL (Structured Query Language) (sql)

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

IF r_contact1 = r_contact2 THEN ... END IF;
Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Nested record

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

The following example declares a record type named address. Then in 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: SQL (Structured Query Language) (sql)

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

Was this tutorial helpful?