PL/SQL Records

PL/SQL stands for Procedural Language/Structured Query Language. It is an extension of the Structured Query Language (SQL). SQL is used in the Oracle databases for procedural programming. The main feature of PL/SQL is it can work with the composite data types, like records. PL/SQL records can be provided the binding the data into a single unit. It consists of multiple fields and each field has its data type and its name, it defines the structure of the record. These records are declared at the package, block, or schema level. PL/SQL records are allowed to developers for groups of the data under a single name, creating a structured and manageable way to handle data within the programs.

PL/SQL Records

Records are nothing but it is allowed to the group of related pieces of data under a single name. Records are also called structures in C language, objects in Java language, and tuples in Python language. They organized the data and managed the data into a structured format, enhancing the code readability and maintainability. Records are the fundamental data structures. It plays an important role in managing the data or organizing the data in programs.

Declaration of records with 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 for declaring 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

Output

Declaration of Records With 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.

Programmer-Defined Record

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.

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

In PL/SQL, records are the powerful constructs that play a crucial role in database programming. It enables the developers to manage effective data, enhance the code organization, and build robust and scalable applications within the Oracle ecosystems. These are the fundamental constructs that provide the structured approach to handling and manipulating the data within the Oracle databases. They offered different advantages like modularity, data organization, abstraction, flexibility, hierarchical data modeling, and integration with SQL.