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 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.
You define and declare records in the declaration section of a block or via package specification.
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;
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;
To declare a cursor-based record, you use the
%ROWTYPE attribute with an explicit cursor as shown below:
DECLARE record_name cursor_name%ROWTYPE;
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;
In this example:
- First, declare an explicit cursor that fetches data from the
phonecolumns of the
- Second, declare a record named
r_contactwhose structure is the same as the
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:
- 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], ... );
To declare a record based on the predefined record type, you use the following syntax:
The following example defines a record type whose name is
customer_contacts and a record whose type is
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;
Referencing a record’s field
You reference a field in a record via the dot notation as shown below:
For example, to reference the
first_name field of the
r_contact record, you use the following expression:
You can assign a record to another record of the same type, for example:
r_contact1 := r_contact2;
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;
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;
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)';
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;
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;
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) );
The following block inserts a new row into the
persons table using a
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;
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;
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
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;
In this tutorial, you have learned about PL/SQL record and how to use it effectively to simplify your code.