PL/SQL Records

PL/SQL stands for Procedural Language/Structured Query Language. It is an extension of the Structured Query Language (SQL). A core feature of PL/SQL is its ability to work with complex data types, including PL/SQL records. PL/SQL records enable developers to group related data elements, creating a single unit that can be manipulated as a cohesive structure. This approach simplifies data management and enhances code modularity, readability, and maintainability within Oracle’s powerful database system.

In this article, we’ll explore what PL/SQL records are, their types, benefits, and usage through examples to help you efficiently work with structured data in Oracle.

What Are PL/SQL Records?

In PL/SQL, records are structured data types that allow grouping multiple, related data fields under a single name. Records are similar to structures in C, objects in Java, and tuples in Python. Each record has multiple fields, and each field has a data type and a name. Records help developers organize data logically, making it easier to work with and read. Records can be declared at the package, block, or schema level and used in various PL/SQL contexts.

Types of PL/SQL Records

PL/SQL offers different ways to declare and use records. Here’s an overview of the common types:

1. Table-Based Records: Linked to a table’s row structure, allowing you to work directly with database table columns.
2. Cursor-Based Records: Derived from a cursor’s query result set, providing a way to process query results one row at a time.
3. Programmer-Defined Records: User-defined records with custom fields and data types.
4. Nested Records: Records within records, enabling hierarchical data management

1. Declaring Table-based records

In database systems, it can declare the records using table-based records. Table-based records are the data structures that resemble the tables or rows in a database table. Each record contains the fields i.e. columns with their corresponding values.

Example: Declaring and Using a Table-Based Record

record Human {
  id: Integer,
  name: String,
  age: Integer,
  ph_no: Integer
}

In the above example:

Here,

• The name of the record is 'Human'.
• 'id', 'name', 'age', and 'ph_no' are the fields of the record.
• Every field contains its data type such as 'Integer' or 'String' and it indicates the data type.

Now, we are creating instances for this record and inserting the data.

Human1 = Human {
     id: 1,
     name: "John",
     age: 25,
     ph_no: 9124567833
}
Human2 = Human {
    id: 2,
    name: "Amar",
    age: 26,
    ph_no: 9874567321
}

In databases, records are called rows in a table, and we can interact with them using SQL queries.

Example for creating a table in SQL database to represent the 'Human' record:

CREATE TABLE Human (
    id INT PRIMARY KEY,
    name VARCHAR (150),
    age INT,
    ph_no INT
);

After that, we can insert records into the table using SQL 'INSERT' statement:

INSERT INTO Human (id, name, age, ph_no) VALUES (1 , 'John' , 25 , 9124567833) ;
INSERT INTO Human (id, name, age, ph_no) VALUES (2 , 'Amar' , 26 , 9874567321) ;

Output: match

2. Declaring Cursor-Based Records

In a database management system, a cursor is an object of the database that is used to manipulate the data or retrieve the data row by row, typically within a procedural language like PL/SQL.

Here is an example of records with Cursor-based records:

DECLARE
    -- Declare a cursor to fetch data
    CURSOR employee_cursor IS
        SELECT employee_id, employee_name, salary
        FROM employees
        WHERE department_id = 10;

    -- Declare a record type based on the cursor's query structure
    TYPE employee_record_type IS RECORD (
        emp_id   employees.employee_id%TYPE,
        emp_name employees.employee_name%TYPE,
        emp_salary employees.salary%TYPE
    );

    -- Declare a variable of the record type
    employee_rec employee_record_type;

BEGIN
    -- Open the cursor
    OPEN employee_cursor;

    -- Fetch data from the cursor and process each record
    LOOP
        FETCH employee_cursor INTO employee_rec;
        EXIT WHEN employee_cursor%NOTFOUND;

        -- Process the record (for example, print the values)
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_rec.emp_id || ', Name: ' || employee_rec.emp_name || ', Salary: ' || employee_rec.emp_salary);
    END LOOP;

    -- Close the cursor
    CLOSE employee_cursor;
END;

In the above example:

• We can declare the cursor as 'employee_cursor', which selects the employee data from the 'employee' table.
• 'employee_record_type' is defined as a record type to match the structure of data and it will be returned by the cursor.
• A variable 'employee_rec' of the type 'employee_record_type' is declared inside the 'BEGIN' block.
• When the cursor is open, the loop iterates with the cursor's data.
• Data is fetched into the 'employee-rec' variable in the given loop by the programmer and each value is printed.
• The loop prints the values or records until the records are not found.

3. Programmer-Defined Record (User-Defined)

A programmer-defined record is also called a user-defined record. It is a data structure created by the programmer to store the pieces of data of different types in a single unit. These records are user-defined. these are not predefined records in a programming language or computer system.

These records are defined by the programmer according to the needs of the application. These records are mainly used in programming languages that support the composite data types like structures in C/C++, and classes in Java, Python, and C#.

# Define a programmer-defined record representing a person
class Human:
    def __init__(self, name, age, address):
        self.name = name
        self.age = age
        self.address = address

# Create an instance of the Person record
Human1 = Human("Jagan Malla", 30, "Runku Street")

# Access fields of the record
print("Name:", Human1.name)
print("Age:", Human1.age)
print("Address:", Human1.address)

In the above example, 'Human' is the programmer-defined record with the fields of the 'name', 'age', and 'address'. An instance of the 'person1' record will be created with the specific values for each field. This record hides the information about the person into a single object.

Referencing a Record's Field

Referencing a record's field involves accessing or manipulating the individual components of the record variable in PL/SQL. Records in the PL/SQL are equal to the tuples in the Python programming language. These are allowed to the group of data taken under the single variable name.

Here is the example for the reference a record's field in PL/SQL:

Declare the Record: First of all we need to declare the record type based on the user-defined type or structure of the table.

DECLARE
    -- Declare a record based on a table structure
    TYPE employee_record_type IS RECORD (
        emp_id   employees.employee_id%TYPE,
        emp_name employees.employee_name%TYPE,
        emp_salary employees.salary%TYPE
    );
-- Declare a record variable
    employee_rec employee_record_type;

Access Record Fields: After declaring the record variable, we can access the separated fields using the dot notation.

-- Assign values to the record fields
employee_rec.emp_id := 101;
employee_rec.emp_name := 'John Doe';
employee_rec.emp_salary := 50000;

-- Retrieve values from the record fields
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_rec.emp_id);
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_rec.emp_name);
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || employee_rec.emp_salary);

Manipulate Record Fields: We can also perform the calculations and operations with the record fields.

-- Increase employee salary by 10%
employee_rec.emp_salary := employee_rec.emp_salary * 1.1;

Pass Record as Parameter: Records are passed the parameters to the functions or procedures allowing the work with the structured data with the subprograms.

-- Example of a procedure accepting a record parameter
PROCEDURE print_employee_details(emp_record IN employee_record_type) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.emp_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_record.emp_salary);
END;

Cursor Fetch Into Record: We can retrieve the data from the cursor directly into the variables of the record.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, employee_name, salary
        FROM employees;
     emp_rec employee_record_type;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO emp_rec;
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_rec.emp_name);
    DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_rec.emp_salary);
END;

Assigning records

Assigning records in PL/SQL involved copying the content from one record variable into another record variable. This process allows the duplicate data stored in the record and it is available for manipulating the data without affecting the original data in the records.

Here is an example for assigning the record variables. we can assign the content of record variables to another record with the help of the assignment operator ( ':= ' ).

 -- Assigning values to fields of emp_rec
emp_rec.emp_id := 101;
emp_rec.emp_name := 'John Doe';
emp_rec.emp_salary := 50000;

-- Assigning emp_rec to emp_rec1
emp_rec1 := emp_rec;

Records with INSERT Statement

In PL/SQL, records are used with INSERT statements is insert data in the database tables. Records are allowed to be organized and manipulate the data in a structured manner. It will make the work with many fields as a single unit.

Here is the example for the INSERT Statement:

DECLARE
    -- Define a record type
    TYPE employee_record_type IS RECORD (
        emp_id   employees.employee_id%TYPE,
        emp_name employees.employee_name%TYPE,
        emp_salary employees.salary%TYPE
    );

    -- Declare a record variable
    emp_rec employee_record_type;

BEGIN
    -- Assign values to the record fields
    emp_rec.emp_id := 101;
    emp_rec.emp_name := 'John Doe';
    emp_rec.emp_salary := 50000;

    -- Insert data into the employees table using the record
    INSERT INTO employees (employee_id, employee_name, salary)
    VALUES (emp_rec.emp_id, emp_rec.emp_name, emp_rec.emp_salary);

    -- Commit the transaction
    COMMIT;
END;
/

Records with UPDATE Statement

In PL/SQL, records are used with UPDATE statements is update the existing data in the database tables. Records are allowed to be organized and manipulate the data in a structured manner. It will make the work with many fields as a single unit.

DECLARE
    -- Define a record type
    TYPE employee_record_type IS RECORD (
        emp_id   employees.employee_id%TYPE,
        emp_name employees.employee_name%TYPE,
        emp_salary employees.salary%TYPE
    );

    -- Declare a record variable
    emp_rec employee_record_type;

BEGIN
    -- Assign values to the record fields
    emp_rec.emp_id := 101;
    emp_rec.emp_name := 'John Doe';
    emp_rec.emp_salary := 55000; -- Updated salary

    -- Update data in the employees table using the record
    UPDATE employees
    SET employee_name = emp_rec.emp_name,
        salary = emp_rec.emp_salary
    WHERE employee_id = emp_rec.emp_id;

    -- Commit the transaction
    COMMIT;
END;
/

Here, in the above INSERT and UPDATE Statements examples:

• we can define the record type 'employee_record_type' that matches with the structure of the 'employee' table.
• A record variable 'emp_rec' is declared as 'employee_record_type'.
• The values are assigned to the fields of the variables of the record.
• For the INSERT statement, we can use record fields directly in the values of the table.
• For the UPDATE statement, we can use record fields to update the corresponding columns in the table.

4. Nested Record

A nested record is nothing but the type of record that is used for the modeling of difficult data structures where the entities have more than one attribute. They helped the organize data hierarchically, will made it easier to manage and manipulate the records within the PL/SQL programs.

Here is the example for the Nested Record:

DECLARE
    -- Define a nested record type
    TYPE address_record_type IS RECORD (
        street_address VARCHAR2(100),
        city VARCHAR2(50),
        state VARCHAR2(50),
        postal_code VARCHAR2(20)
    );

    TYPE employee_record_type IS RECORD (
        emp_id   INTEGER,
        emp_name VARCHAR2(100),
        emp_address address_record_type -- Nested record type
    );

    -- Declare a record variable
    emp_rec employee_record_type;

BEGIN
    -- Assign values to the fields of the nested record
    emp_rec.emp_id := 101;
    emp_rec.emp_name := 'John Doe';
    emp_rec.emp_address.street_address := '123 Main St';
    emp_rec.emp_address.city := 'New York';
    emp_rec.emp_address.state := 'NY';
    emp_rec.emp_address.postal_code := '10001';

    -- Output the nested record data
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_rec.emp_name);
    DBMS_OUTPUT.PUT_LINE('Street Address: ' || emp_rec.emp_address.street_address);
    DBMS_OUTPUT.PUT_LINE('City: ' || emp_rec.emp_address.city);
    DBMS_OUTPUT.PUT_LINE('State: ' || emp_rec.emp_address.state);
    DBMS_OUTPUT.PUT_LINE('Postal Code: ' || emp_rec.emp_address.postal_code);
END;
/

In the above example:

• We can define the two record types i.e. 'address_record_type' and 'employee_record_type'.
• 'address_record_type' represents the address with the fields of street address, state, city, and postal code.
• 'employee_record_type' represents the employee with the fields of employee ID, address, and name.
• We can declare the record variable 'emp_rec' type of 'employee_record_type'.
• We assign the values to fields of the nested record using the dot notation.
• At last, the output of the data is stored in the nested record.

Conclusion

PL/SQL records are a powerful construct in Oracle database programming, enabling the management of structured data, improving modularity, and making code maintenance easier. By understanding the various types of records—table-based, cursor-based, programmer-defined, and nested—developers can handle complex data relationships efficiently within Oracle databases. Additionally, these records streamline data manipulation operations, supporting efficient INSERT, UPDATE, and retrieval tasks within PL/SQL blocks.