Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



Records in PL/SQL

A PL/SQL record is a data structure composed of multiple pieces of information called fields . To use a record, you must first define it and declare a variable of this type.

Types of Record

There are three types of records

  • Table-based
  • Cursor-based
  • Programmer-defined

Declaring Records

To declare a record, you must first define a composite datatype; then declare a record for that type.

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 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 employees table in HR sample database.
  • Second, we used the SELECT statement to retrieve the employee information of the employee id 200 and populate the data into the r_emp record .
  • Third, we print out the first name of the selected employee from the r_emp employee record.

Declaring Programmer-Defined Record TYPEs

You declare a record type with the record TYPE statement. The TYPE statement specifies the name of the new record structure, and the components or fields which make up that record.

The general syntax of the record TYPE definition is:

TYPE <type_name> IS RECORD
(<field_name1> <datatype1>,
<field_name2> <datatype2>,
...
<field_nameN> <datatypeN>
);

where is the name of the Nth field in the record and is the datatype of that Nth field. The datatype of a record's field can be any of the following:

  • Pre-defined datatype (VARCHAR2, NUMBER, etc.)
  • Programmer-defined subtype (PL/SQL Release 2.1 and above)
  • Declarations using %TYPE attributes
  • Declarations using %ROWTYPE attributes
  • PL/SQL record type
  • PL/SQL table type

Examples of Programmer-Defined Record Declarations
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 SELECT statement.

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;
/


Related Videos