Summary: in this tutorial, you will learn about the PL/SQL record that is a composite data structure, which allows you to manage your data in program more efficiently.
What is a PL/SQL Record
A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type.
Declaring a PL/SQL Record
PL/SQL provides three ways to declare a record: table-based record, cursor-based record and programmer-defined records.
Declaring Table-based Record
To declare a table-based record you use a table name with
%ROWTYPE attribute. The fields of the PL/SQL record has the same name and data type as the column of the table.
The following illustrates table-based record declaration:
DECLARE table_based_record table_name%ROWTYPE;
After having the table-based record, you can use it in various ways, for example in SQL SELECT statement as follows:
SET SERVEROUTPUT ON SIZE 1000000; DECLARE r_emp employees%ROWTYPE; n_emp_id employees.employee_id%TYPE := 200; BEGIN SELECT * INTO r_emp FROM employees WHERE employee_id = n_emp_id; -- print out the employee's first name DBMS_OUTPUT.PUT_LINE(r_emp.first_name); END; /
In the above example:
- First, we defined a record based on
employeestable in HR sample database.
- Second, we used the
SELECTstatement to retrieve the employee information of the employee id
200and populate the data into the
- Third, we print out the first name of the selected employee from the
Declaring Programmer-defined Record
To declare programmer-defined record, first you have to define a record type by using
TYPE statement with the fields of record explicitly. Then, you can declare a record based on record type that you’ve defined.
The following illustrates the syntax of the defining programmer-defined record with
TYPE type_name IS RECORD (field1 data_type1 [NOT NULL] := [DEFAULT VALUE], field2 data_type2 [NOT NULL] := [DEFAULT VALUE], ... fieldn data_type3 [NOT NULL] := [DEFAULT VALUE] );
The data type of field can be any of the following:
- Scalar type (
- Anchor declaration
%ROWtype, in this case we have a nested record.
- PL/SQL collection types.
- Cursor variable
Once you define the record type, you can declare a record based on the record type as follows:
The following example demonstrates how to declare programmer-defined record:
SET SERVEROUTPUT ON SIZE 1000000; DECLARE TYPE t_name IS RECORD( first_name employees.first_name%TYPE, last_name employees.last_name%TYPE ); r_name t_name; -- name record n_emp_id employees.employee_id%TYPE := 200; BEGIN SELECT first_name, last_name INTO r_name FROM employees WHERE employee_id = n_emp_id; -- print out the employee's name DBMS_OUTPUT.PUT_LINE(r_name.first_name || ',' || r_name.last_name ); END; /
Declaring Cursor-based Record
You can define a record based on a cursor. First, you must define a cursor. And then you use
%ROWTYPE with the cursor variable to declare a record. The fields of the record correspond to the columns in the cursor
The following is an example of declaring a record based on a cursor.
SET SERVEROUTPUT ON SIZE 1000000; DECLARE CURSOR cur_emp IS SELECT * FROM employees WHERE employee_id = 200; emp_rec cur_emp%ROWTYPE; BEGIN NULL; END; /
Working with PL/SQL Record
After having a PL/SQL record, you can work with a record as a whole or you can work with individual field of the record.
Working with PL/SQL record at record level
At record level, you can do the following:
- You can assign a PL/SQL record to another PL/SQL record. The pair of PL/SQL records must have the same number of fields and the data type of each field has to be convertible.
- You can assign a PL/SQL record
NULLvalue by assigning an uninitialized record.
- A PL/SQL record can be used as an argument of parameter in a function
- You can return a PL/SQL record from a function
- To check if the record is NULL, you have to check each individual field of the record.
- To compare two records, you have to compare each individual field of each record.
Here is an example of working with PL/SQL record at record level:
SET serveroutput ON SIZE 1000000; DECLARE TYPE t_name IS RECORD( first_name employees.first_name%TYPE, last_name employees.last_name%TYPE ); r_name t_name; r_name2 t_name; r_name_null t_name; n_emp_id employees.employee_id%TYPE := 200; BEGIN -- assign employee's infomation to record SELECT first_name, last_name INTO r_name FROM employees WHERE employee_id = n_emp_id; -- assign record to another record r_name2 := r_name; -- print out the employee's name DBMS_OUTPUT.PUT_LINE(r_name2.first_name || ',' || r_name2.last_name); -- assign record to NULL r_name2 := r_name_null; -- check NULL for each individual field IF r_name2.first_name IS NULL AND r_name2.last_name IS NULL THEN DBMS_OUTPUT.PUT_LINE('Record r_name2 is NULL'); END IF; END; /
Working with PL/SQL record at field level
As you see in the above example, we can reference to a field of a record by using dot notation (.) as follows:
If you reference to a record variable in different package or schema you need to explicitly specify those information as shown below:
You can use the assignment operator (
:=) to change the value of field of a record that you reference to.
For the nested record you need to use extra dot notation (
The following example demonstrates how to use PL/SQL record a field level:
DECLARE TYPE t_address IS RECORD( house_number VARCHAR2(6), street VARCHAR2(50), phone VARCHAR2(15), region VARCHAR2(10), postal_code VARCHAR2(10), country VARCHAR2(25) ); TYPE t_contact IS RECORD( home t_address, business t_address ); r_contact t_contact; BEGIN r_contact.business.house_number := '500'; r_contact.business.street := 'Oracle Parkway'; r_contact.business.region := 'CA'; r_contact.business.postal_code := '94065'; r_contact.business.country := 'USA'; r_contact.business.phone := '+1.800.223.1711'; END;
In this tutorial, you’ve learned how to use PL/SQL record to manipulate data more efficiently, and to make your code cleaner and easier to maintain.