Playing with PL/SQL %ROWTYPE: Simplifying Row-Based Operations

The %ROWTYPE attribute in PL/SQL allows you to work with entire rows from a table or a cursor. It simplifies operations such as inserting, updating, and manipulating rows without explicitly defining individual variables for each column.

What Is %ROWTYPE in PL/SQL?

• %ROWTYPE provides a record type representing a row in a database table or cursor.
• Variables declared using %ROWTYPE inherit the structure of the table or cursor, including all its columns.

Key Benefits of %ROWTYPE

1. Simplifies Code: Eliminates the need to declare individual variables for each column.
2. Dynamic Adjustments: Automatically adapts to changes in the table structure.
3. Improved Readability: Reduces the verbosity of SQL operations in PL/SQL blocks.

Example 1: Inserting Data Using %ROWTYPE

The following procedure demonstrates how to retrieve a row from the EMPLOYEES table and insert it into the RETIRED_EMPS table:

Procedure: INSERT_REC

PROCEDURE INSERT_REC
AS
    V_EMP_NUMBER NUMBER := 124;
    V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('INSERT_REC');
    SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

    INSERT INTO RETIRED_EMPS
    (EMP_NO, FIRST_NAME, LAST_NAME,
     EMAIL, PHONE_NUMBER, HIRE_DATE,
     JOB, SAL, COMM,
     MGR, DEPT_NO)
    VALUES
    (V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME,
     V_EMP_REC.EMAIL, V_EMP_REC.PHONE_NUMBER, V_EMP_REC.HIRE_DATE,
     V_EMP_REC.JOB_ID, V_EMP_REC.SALARY, V_EMP_REC.COMMISSION_PCT,
     V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);
END INSERT_REC;

Explanation:

• The row for EMPLOYEE_ID = 124 is fetched into V_EMP_REC.
• Data is inserted into the RETIRED_EMPS table column by column.

Example 2: Bulk Insert Using %ROWTYPE

This procedure simplifies the bulk insert process by directly using a %ROWTYPE variable:

Procedure: INSERT_REC_BULK

PROCEDURE INSERT_REC_BULK
AS
    V_EMP_NUMBER NUMBER := 124;
    V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('INSERT_REC_BULK');
    SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

    -- Modify the record fields as needed
    V_EMP_REC.COMMISSION_PCT := 0.1;
    V_EMP_REC.EMPLOYEE_ID := V_EMP_NUMBER * 10;

    -- Insert the entire record
    INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;
END INSERT_REC_BULK;

Explanation:

• The entire V_EMP_REC record is inserted directly into the RETIRED_EMPS table, saving effort and improving readability.

Example 3: Updating Data Using %ROWTYPE

This example shows how to fetch a record, modify its values, and update the table using the entire record:

Procedure: UPDATE_REC_BULK

PROCEDURE UPDATE_REC_BULK
AS
    V_EMP_NUMBER NUMBER := 1240;
    V_EMP_REC RETIRED_EMPS%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('UPDATE_REC_BULK');
    SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;

    -- Modify the record fields
    V_EMP_REC.COMM := 0.9;

    -- Update the table using the modified record
    UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE EMP_NO = V_EMP_NUMBER;
END UPDATE_REC_BULK;

Explanation:

• The row for EMP_NO = 1240 is fetched into V_EMP_REC.
• Fields are modified, and the entire row is updated in one statement.

Key Takeaways

1. Flexible Row Handling: %ROWTYPE simplifies working with entire rows, making it ideal for operations involving multiple columns.
2. Direct Updates and Inserts: Use %ROWTYPE to directly insert or update rows with minimal code.
3. Reduced Maintenance: Changes to table structure automatically reflect in %ROWTYPE variables, reducing the need for manual updates.

Conclusion

PL/SQL’s %ROWTYPE attribute is a powerful tool for row-based operations. It minimizes redundancy, improves code clarity, and enhances maintainability, especially in scenarios involving frequent row manipulations.

Would you like more examples or insights into advanced PL/SQL techniques? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio