RECORD Types

 

A PL/SQL RECORD is a composite data structure that groups multiple related fields together under a single variable. It works like a row of a table or a struct in C.

Instead of declaring multiple variables, you can use one record that holds multiple fields.

Useful when handling table rows, joining multiple columns, or passing multiple values in a single variable

 

There are three types of records in PL/SQL:

1.       Table-Based RECORD (Uses %ROWTYPE)

2.       User-Defined RECORD (Custom-defined structure)

3.       Cursor-Based RECORD (Uses %ROWTYPE with Cursors)

 

1. Table-Based RECORD (%ROWTYPE)

Used when a record matches the structure of a table row.

It automatically takes the column names and data types from a table.

Example: Using %ROWTYPE to Store a Row from a Table

DECLARE

    emp_record employees%ROWTYPE;  -- Declares a record matching the employees table

BEGIN

    -- Fetch employee details into the record

    SELECT * INTO emp_record FROM employees WHERE emp_id = 101;

 

    -- Accessing record fields

    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.emp_name || ' Salary: ' || emp_record.salary);

END;

/

Key Points:

✅ Matches table structure
✅ Easy to use when working with entire rows
✅ Auto-updates if the table structure changes

 

2. User-Defined RECORD (Custom RECORD Type)

You can define a custom record with specific fields that may not match an exact table structure.

Useful for joining multiple tables or combining different data types.

Example: Declaring and Using a User-Defined RECORD

DECLARE

    -- Define a custom record type

    TYPE emp_record_type IS RECORD (

        emp_id employees.emp_id%TYPE,

        emp_name employees.emp_name%TYPE,

        salary employees.salary%TYPE

    );

   

    -- Declare a record variable of this type

    emp_data emp_record_type;

BEGIN

    -- Assign values manually

    emp_data.emp_id := 101;

    emp_data.emp_name := 'John Doe';

    emp_data.salary := 50000;

 

    -- Display record data

    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_data.emp_name || ' Salary: ' || emp_data.salary);

END;/

Key Points:

✅ Allows custom structures
✅ Can hold data from multiple tables
✅ Independent of any table

 

3. Cursor-Based RECORD (%ROWTYPE with Cursors)

When fetching data using explicit cursors, we can use record variables to store row values.

Example: Using RECORD with a Cursor

DECLARE

    -- Declare a record variable based on the employees table

    emp_data employees%ROWTYPE;

   

    -- Declare a cursor

    CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id = 10;

BEGIN

    -- Open cursor and fetch data

    OPEN emp_cursor;

    FETCH emp_cursor INTO emp_data;

 

    -- Display record values

    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_data.emp_name || ' Salary: ' || emp_data.salary);

 

    -- Close cursor

    CLOSE emp_cursor;

END;

/

Key Points:

✅ Works well with cursors
✅ Handles one row at a time
✅ Best suited for explicit cursor operations

 

4. Using RECORD Inside a Collection (Nested RECORDs)

You can store multiple records inside a collection (table type variable).

Example: Using RECORD with BULK COLLECT

DECLARE

    -- Define a record type

    TYPE emp_record_type IS RECORD (

        emp_id employees.emp_id%TYPE,

        emp_name employees.emp_name%TYPE

    );

 

    -- Define a table type (nested collection of records)

    TYPE emp_table_type IS TABLE OF emp_record_type;

 

    -- Declare a collection of records

    emp_list emp_table_type;

BEGIN

    -- Fetch multiple rows at once

    SELECT emp_id, emp_name BULK COLLECT INTO emp_list FROM employees WHERE department_id = 20;

 

    -- Loop through the collection

    FOR i IN 1..emp_list.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_list(i).emp_name);

    END LOOP;

END;

/

Key Points:

✅ Combines collections and records
✅ Efficient when fetching multiple rows at once

 

5. Comparing Different RECORD Types

Feature	%ROWTYPE	User-Defined RECORD	Cursor-Based RECORD
Definition	Matches a table row	Custom-defined structure	Based on Cursor
Flexibility	Matches table structure exactly	Custom fields, multiple sources	Matches cursor query
Usage	Simple row storage	Combines multiple tables/fields	Fetching rows from cursors
Memory Usage	Less	More	Less

 

6. When to Use Each Type?

Scenario	Best RECORD Type
Fetching all columns from a single table row	%ROWTYPE
Creating a record structure that doesn’t match a table	User-Defined RECORD
Fetching data using explicit cursors	Cursor-Based RECORD
Storing multiple records in memory	Nested RECORDs in Collections

 

Conclusion:

·       PL/SQL RECORDs allow you to store multiple fields in a single variable.

·       They simplify row handling and reduce the number of variables needed.

·       Best used in scenarios involving table rows, cursor fetching, and custom data structures.