PL/SQL Records, Collections & Cursors: Data Handling & Control

TAKEAWAYS:

• Records in PL/SQL mirror database rows but allow in-memory manipulation before database commits.
• Collections (associative arrays, nested tables) store heterogeneous data dynamically, replacing multiple variables.
• Cursors enable row-by-row processing of query results, with explicit control over iteration (OPEN-FETCH-CLOSE).
• Explicit cursors (user-defined) replace implicit cursors (SQL*Plus defaults) for fine-grained result handling.
• Collections reduce procedural code by bundling related data (e.g., inventory items, order details).
• Cursor FOR loops simplify iteration with automatic OPEN/FETCH/CLOSE, but explicit cursors offer full control.

1. PL/SQL Records: Structured Data in Memory

Definition: A record is a PL/SQL data type that groups multiple fields (like a database row) into a single variable. Unlike database rows, records exist in memory and can be modified before committing to the database.

How It Works:

• Declared using %ROWTYPE (to mirror a table’s structure) or explicitly with field names and types.
• Fields can be accessed like an object (e.g., record_name.field_name).
• Used to pass row data between PL/SQL blocks or store intermediate results.

Example: Employee Record

classDiagram
    EmployeeRecord : +String emp_id
    EmployeeRecord : +String emp_name
    EmployeeRecord : +Number salary

DECLARE
    TYPE emp_rec IS RECORD (
        emp_id   VARCHAR2(10),
        emp_name VARCHAR2(50),
        salary   NUMBER(10,2)
    );
    v_emp emp_rec;
BEGIN
    -- Fetch a row into the record
    SELECT emp_id, emp_name, salary INTO v_emp
    FROM employees
    WHERE emp_id = 'E1001';

    -- Modify salary in memory
    v_emp.salary := v_emp.salary * 1.1; -- 10% raise

    -- Commit changes (if needed)
    UPDATE employees
    SET salary = v_emp.salary
    WHERE emp_id = 'E1001';
END;

Advantages:

• Memory efficiency: Avoids repeated database calls for small updates.
• Readability: Clearly defines related data fields.
• Flexibility: Can be used in collections or passed as parameters.

Disadvantages:

• Static structure: Fields cannot be added/removed dynamically (unlike collections).
• Limited to one row: For multi-row operations, use cursors or collections.

2. PL/SQL Collections: Dynamic Data Storage

Definition: Collections are variable-sized arrays in PL/SQL that store heterogeneous data (unlike arrays, which require fixed types). Two types:

1. Associative Arrays (indexed by strings/numbers).
2. Nested Tables (ordered, indexed by position).

How It Works:

• Declared with TYPE and VARRAY (variable-length array) or TABLE (ordered table).
• Elements accessed via collection_name(index).
• Used to return multiple rows from procedures or store intermediate results.

Example: Inventory Collection

classDiagram
    InventoryCollection : +VARRAY<STRING> item_names
    InventoryCollection : +VARRAY<NUMBER> quantities

DECLARE
    TYPE item_varray IS VARRAY(10) OF VARCHAR2(50);
    TYPE qty_varray IS VARRAY(10) OF NUMBER;

    v_items item_varray := item_varray('Laptop', 'Mouse', 'Keyboard');
    v_quantities qty_varray := qty_varray(10, 50, 20);
BEGIN
    -- Display inventory
    FOR i IN 1..v_items.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_items(i) || ': ' || v_quantities(i));
    END LOOP;
END;

Comparison Table: Collections vs. Records

Real-World Use: Daraz Order Processing Daraz uses collections to bundle multiple product items in a single order. For example:

• A customer orders:
  • 2 laptops (item_names[1..2] = 'Laptop')
  • 1 mouse (item_names[3] = 'Mouse')
• Quantities are stored in a parallel collection (quantities[1] = 2, quantities[2] = 1).
• The system processes payments and inventory updates in bulk using these collections.

3. Cursors: Row-by-Row Processing

Definition: A cursor is a pointer to a SQL query result set, allowing row-by-row processing in PL/SQL. Two types:

1. Implicit Cursors: Default in SQL*Plus (e.g., SELECT INTO).
2. Explicit Cursors: User-defined for complex processing (e.g., loops, bulk operations).

How It Works:

1. DECLARE: Define the cursor with a SQL query.
2. OPEN: Execute the query.
3. FETCH: Retrieve rows one by one.
4. CLOSE: Release resources.

Example: Employee Salary Report

sequenceDiagram
    participant PL/SQL
    participant DB
    PL/SQL->>DB: DECLARE emp_cursor CURSOR FOR SELECT * FROM employees;
    PL/SQL->>DB: OPEN emp_cursor;
    loop For each row
        PL/SQL->>DB: FETCH emp_cursor INTO v_emp;
        PL/SQL->>PL/SQL: Process v_emp (e.g., print salary)
    end
    PL/SQL->>DB: CLOSE emp_cursor;

DECLARE
    CURSOR emp_cursor IS SELECT emp_id, emp_name, salary FROM employees;
    v_emp employees%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND; -- Exit if no more rows
        DBMS_OUTPUT.PUT_LINE(v_emp.emp_name || ': ' || v_emp.salary);
    END LOOP;
    CLOSE emp_cursor;
END;

Explicit vs. Implicit Cursors

Real-World Use: Bank Loan Processing A bank uses explicit cursors to process loan applications dynamically:

1. A cursor fetches all pending loans (SELECT * FROM loans WHERE status = 'Pending').
2. For each loan, the system checks credit scores and updates the database.
3. The cursor ensures only one loan is processed at a time, avoiding race conditions.

4. Collections in Practice: Associative Arrays

Definition: An associative array is a collection indexed by strings or numbers (not just sequential integers). Useful for mapping keys to values (e.g., employee IDs to salaries).

Example: Employee Salary Map

classDiagram
    SalaryMap : +ASSOCIATIVE ARRAY<STRING, NUMBER>
    SalaryMap : +'E1001' --> 50000
    SalaryMap : +'E1002' --> 60000

DECLARE
    TYPE emp_salary_map IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    v_salaries emp_salary_map;
BEGIN
    -- Populate the map
    v_salaries('E1001') := 50000;
    v_salaries('E1002') := 60000;

    -- Retrieve a salary
    DBMS_OUTPUT.PUT_LINE('E1001 salary: ' || v_salaries('E1001'));
END;

Advantages:

• Key-based access: Faster than sequential arrays for large datasets.
• Dynamic sizing: Add/remove elements without redeclaring.
• Flexibility: Indexes can be strings (e.g., employee IDs).

Disadvantages:

• Memory overhead: Each element stores its key and value.
• No ordering: Unlike nested tables, elements are not ordered.

5. Cursors FOR Loops: Simplified Iteration

Definition: A cursor FOR loop automates the OPEN/FETCH/CLOSE cycle, reducing boilerplate code.

Syntax:

FOR record_variable IN cursor_name LOOP
    -- Process each row
END LOOP;

Example: Display All Employees

DECLARE
    CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.emp_id || ': ' || emp_rec.emp_name);
    END LOOP;
    -- No need for OPEN/CLOSE; cursor is closed automatically
END;

When to Use:

• Simple iteration: When you don’t need manual control over fetching.
• Read-only operations: Avoids explicit error handling for %NOTFOUND.

Limitations:

• No manual control: Cannot fetch rows out of order or skip rows.
• Less flexible: Cannot use FETCH with conditions like WHEN OTHERS.

In the Real World

1. Khalti Payment Gateway:

   • Uses collections to bundle multiple payment methods (e.g., credit card, UPI, eSewa) into a single transaction request.
   • Example: A user’s payment options are stored in a VARRAY of payment_methods, which the system processes sequentially.

2. NTC Traffic Management:

   • Cursors dynamically fetch real-time traffic data (e.g., congestion levels) from sensors.
   • The system uses an explicit cursor to process each sensor’s data, updating traffic lights in real time.

3. Ncell Call Routing:

   • Associative arrays map phone numbers to subscriber details (e.g., v_subscribers('9812345678') returns a record).
   • When a call comes in, the system quickly looks up the subscriber’s plan and routes the call accordingly.

Exam Tip

• Focus on syntax: Explicit cursors (DECLARE CURSOR...) and collections (TYPE...VARRAY) are high-weight topics. Memorize their declarations and usage.
• Compare implicit vs. explicit cursors: Always include a table like the one above to show differences.
• Worked examples: Practice writing a cursor FOR loop and a collection-based procedure (e.g., updating salaries for all employees in a department).
• Collections > Records: For multi-row operations, collections are almost always the right tool. Records are for single-row manipulation.
• Error handling: Include WHEN OTHERS in cursor examples to handle %NOTFOUND gracefully.
• Real-world tie-ins: Relate collections to Daraz orders or cursors to bank transactions. Examiners love practical examples.

A flowchart showing the steps of an explicit cursor (DECLARE → OPEN → FETCH → LOOP → CLOSE).

A table comparing associative arrays (key-value pairs) and nested tables (ordered lists) with icons for each.

A side-by-side diagram of a SQL cursor (implicit) and a PL/SQL cursor (explicit), highlighting control steps.