Oracle PL/SQL Records

Yusuf SEZER

4 years ago

This article contains information about Oracle PL/SQL Records such as Table Based, Cursor Based and User Defined Records.

What is Record in Oracle?

Data types such as NUMBER, VARCHAR, VARCHAR2 in Oracle PL / SQL are used to store a single data.

However, defining multiple variables related to each other makes the codes complicated.

Record is a Composite data type used to store more than one value in a single variable.

Record Type in Oracle

Record data type definition can be table-based, cursor-based and user-defined.

Table Based Records in Oracle PL/SQL

In this type of definition, Record definition is made by using TABLE_NAME% ROWDTYPE structure.

In the example below, a record data type named PERSON has been created using some columns in the contacts table.

DECLARE
    PERSON contacts%ROWTYPE;
BEGIN
    PERSON.first_name := 'Yusuf';
    PERSON.last_name := 'SEZER';
    PERSON.email := 'yusufsezer@mail.com';
    PERSON.phone := '+905386934533';
    DBMS_OUTPUT.put_line(PERSON.first_name || ' ' || PERSON.last_name || ' - ' || PERSON.email || ' - ' || PERSON.phone);
END;

DECLARE

PERSON contacts%ROWTYPE;

BEGIN

PERSON.first_name := 'Yusuf';

PERSON.last_name := 'SEZER';

PERSON.email := 'yusufsezer@mail.com';

PERSON.phone := '+905386934533';

DBMS_OUTPUT.put_line(PERSON.first_name || ' ' || PERSON.last_name || ' - ' || PERSON.email || ' - ' || PERSON.phone);

END;

Cursor Based Records in Oracle PL/SQL

In this type of definition, the fields defined with Cursor are taken with CURSOR_NAME% ROWDTYPE and a record is defined.

In the example below, the record data type named PERSON was created using the columns indicated by the LIST_TABLE cursor.

DECLARE
    CURSOR LIST_TABLE IS
        SELECT first_name, last_name, email, phone FROM contacts;
  PERSON LIST_TABLE%ROWTYPE;
BEGIN
  PERSON.first_name := 'Yusuf';
  PERSON.last_name := 'SEZER';
  PERSON.email := 'yusufsezer@mail.com';
  PERSON.phone := '+905386934533';
  DBMS_OUTPUT.put_line(PERSON.first_name || ' ' || PERSON.last_name || ' - ' || PERSON.email || ' - ' || PERSON.phone);
END;

DECLARE

CURSOR LIST_TABLE IS

SELECT first_name, last_name, email, phone FROM contacts;

PERSON LIST_TABLE%ROWTYPE;

BEGIN

PERSON.first_name := 'Yusuf';

PERSON.last_name := 'SEZER';

PERSON.email := 'yusufsezer@mail.com';

PERSON.phone := '+905386934533';

DBMS_OUTPUT.put_line(PERSON.first_name || ' ' || PERSON.last_name || ' - ' || PERSON.email || ' - ' || PERSON.phone);

END;

User Defined Records in Oracle PL/SQL

In this type of definition, the variables to be included in the record are defined using the TYPE keyword.

Record Example in Oracle PL/SQL

In the example below, using the TYPE keyword, we have defined a Record named PERSON, which contains the values of first_name, last_name, email, phone.

DECLARE
    TYPE PERSON IS RECORD (
        first_name VARCHAR2(255),
        last_name VARCHAR2(255) NOT NULL DEFAULT 'SEZER',
        email VARCHAR2(255),
        phone VARCHAR2(20)
    );
  K1 PERSON;
BEGIN
  K1.first_name := 'Yusuf';
  -- K1.last_name := 'SEZER';
  K1.email := 'yusufsezer@mail.com';
  K1.phone := '+905386934533';
  DBMS_OUTPUT.put_line(K1.first_name || ' ' || K1.last_name || ' - ' || K1.email || ' - ' || K1.phone);
END;

DECLARE

TYPE PERSON IS RECORD (

first_name VARCHAR2(255),

last_name VARCHAR2(255) NOT NULL DEFAULT 'SEZER',

email VARCHAR2(255),

phone VARCHAR2(20)

);

K1 PERSON;

BEGIN

K1.first_name := 'Yusuf';

-- K1.last_name := 'SEZER';

K1.email := 'yusufsezer@mail.com';

K1.phone := '+905386934533';

DBMS_OUTPUT.put_line(K1.first_name || ' ' || K1.last_name || ' - ' || K1.email || ' - ' || K1.phone);

END;

Record data types previously defined in the user-defined record can also be used. Another important feature of the Record data type is that it can pass parameters to structures that take parameters such as procedure and function. In this way, related parameters can be grouped and processed with a single record.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about records at docs.oracle.com