Understanding Cursors in PL/SQL

A Cursor is a database object that allows row-by-row processing of query results. It acts as a pointer to the result set of a SQL query and is used to retrieve and manipulate data efficiently.

Types of Cursors in PL/SQL

Implicit Cursor
- Created automatically for SELECT INTO, INSERT, UPDATE, and DELETE statements.
- No explicit declaration is required.
- Managed internally and closed once the query execution is complete.
Explicit Cursor
- Defined by the user to handle multi-row queries.
- Requires explicit declaration, opening, fetching, and closing.
- Created and controlled by the programmer.
- Explicit cursors allow fetching rows one-by-one efficiently instead of querying the database multiple times.
- Provides better control over data fetching and processing.

Why Use Explicit Cursors?

More control over row-by-row processing.
Avoids memory overflow issues compared to collections (e.g., working with millions of rows).
Allows efficient iteration through datasets without overloading memory.
Optimized performance by reducing database calls.

Limitations of Cursors

Cursors only move forward (cannot go backward like collections).
Implicit cursors do not allow customization.

Explicit Cursor

declare
  cursor c_emps is select first_name,last_name from employees;
  v_first_name employees.first_name%type;
  v_last_name employees.last_name%type;
begin
  open c_emps;
  fetch c_emps into v_first_name,v_last_name;
  dbms_output.put_line(v_first_name|| ' ' || v_last_name);
  close c_emps;
end;

Cursor Attributes

%FOUND – Returns TRUE if the last fetch returned a row.
%NOTFOUND – Returns TRUE if the last fetch did not return a row.
%ROWCOUNT – Returns the number of rows fetched so far.
%ISOPEN – Returns TRUE if the cursor is open.

Cursor with records

DECLARE
    CURSOR c_emp is select * from EMPLOYEES;
    -- CURSOR c_emp is select first_name,last_name from EMPLOYEES;
    r_emp EMPLOYEES%rowtype;
BEGIN
    OPEN c_emp;
    FETCH c_emp into r_emp;
    -- FETCH c_emp into r_emp.first_name,r_emp.last_name;
    CLOSE c_emp;
    DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
END;

Looping with Cursors

DECLARE
    CURSOR c_emp is select * from EMPLOYEES;
    r_emp EMPLOYEES%rowtype;
BEGIN
    OPEN c_emp;
    LOOP 
        FETCH c_emp into r_emp;
        EXIT WHEN c_emp%notfound;
        DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
    end LOOP;
    CLOSE c_emp; 
END;

While Loop

DECLARE
    CURSOR c_emp is select * from EMPLOYEES;
    r_emp EMPLOYEES%rowtype;
BEGIN
    OPEN c_emp;
    FETCH c_emp into r_emp;
    WHILE c_emp%found LOOP 
        DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
        FETCH c_emp into r_emp;
    end LOOP;
    CLOSE c_emp; 
END;

For In Loop

In this case we don’t need to open ,close and fetch cursor. It automatically managed.

DECLARE
    CURSOR c_emp is select * from EMPLOYEES;
BEGIN
    FOR emp in c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;

BEGIN
    FOR emp in (select * from EMPLOYEES) LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;

Declaring a Cursor with Parameters

Parameters are defined inside parentheses after the cursor name.
Only specify the data type (e.g., VARCHAR2, NUMBER), not the size.

DECLARE
  CURSOR c_emps(p_dept_id NUMBER) IS  
    SELECT first_name, last_name, department_name  
    FROM employees  
    JOIN departments USING (department_id)  
    WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN c_emps(20) LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;

Using Bind Variables

FOR emp IN c_emps(:b_dept_id) LOOP  
  DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);  
END LOOP;

Using Multiple Parameters in a Cursor

DECLARE
  CURSOR c_emps(p_dept_id NUMBER, p_job_id VARCHAR2) IS  
    SELECT first_name, last_name, job_id, department_name  
    FROM employees  
    JOIN departments USING (department_id)  
    WHERE department_id = p_dept_id AND job_id = p_job_id;
BEGIN
    FOR emp IN c_emps(50, 'ST_MAN') LOOP  
        DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name || ' - ' || emp.job_id);  
    END LOOP;
END;

FOR UPDATE Clause in CURSOR

The FOR UPDATE clause is used in cursors to lock selected rows and prevent other users from modifying them until a commit or rollback is executed.
This ensures consistency in operations where multiple updates depend on the initial state of the rows.
When an UPDATE statement is executed, the row is automatically locked until a COMMIT or ROLLBACK is performed.
However, sometimes, it is necessary to lock a group of rows without updating them immediately.
The FOR UPDATE clause locks all selected rows as soon as the cursor is opened, without performing any unnecessary UPDATE operations.

Benefits of Using "FOR UPDATE"

Prevents Unnecessary Updates: Unlike manual locking through updates, FOR UPDATE locks rows efficiently without modifying them.
Avoids Unnecessary Log Entries: Since no actual updates are made, it prevents excessive logging and resource consumption.
Ensures Data Integrity: Prevents other users from modifying crucial data while calculations or operations are in progress.

Handling Locked Rows

If another session has already locked a row, the default behavior is to wait indefinitely until the lock is released.
This can cause delays and inefficiencies, especially if the lock remains for a long time.

Using "NOWAIT" and "WAIT" Options

NOWAIT: If a row is locked, the operation immediately exits with an error instead of waiting.
WAIT n: Specifies a timeout (in seconds) before exiting with an error if the row remains locked.
- Example: WAIT 30 waits for 30 seconds before throwing an error.
- The default behavior of the FOR UPDATE clause is to wait indefinitely if no option is specified.

Selective Row Locking with "FOR UPDATE OF"

When joining multiple tables, FOR UPDATE locks all selected rows from all tables.
If only specific tables or columns should be locked, use FOR UPDATE OF column_name.
- Example: FOR UPDATE OF employees_copy.phone_number, departments.location_id locks only the rows related to these columns.

cursor c_emps is select employee_id,first_name,last_name,department_name
      from employees_copy join departments using (department_id)
      where employee_id in (100,101,102)
      for update of employees_copy.phone_number, 
      departments.location_id nowait;

WHERE CURRENT OF Clause

The WHERE CURRENT OF clause is used in PL/SQL in combination with the FOR UPDATE clause.
It allows updates and deletions on rows currently being processed by a cursor.
This method enhances performance by directly referencing rows using ROWID, avoiding additional lookups via primary keys.

Understanding ROWID

ROWID is a unique identifier for each row, used internally by the database for fast access.
Using ROWID eliminates the need for an index lookup.

SELECT ROWID, e.* FROM employees e;

Cursor Declaration and Using FOR UPDATE

DECLARE CURSOR c_emps IS
    SELECT * FROM employees
    WHERE department_id = 30
    FOR UPDATE;

Traditional Update Using Primary Key

FOR r_emps IN c_emps LOOP
    UPDATE employees
    SET salary = salary + 60
    WHERE employee_id = r_emps.employee_id;
END LOOP;

Optimized Approach Using WHERE CURRENT OF

FOR r_emps IN c_emps LOOP
    UPDATE employees
    SET salary = salary + 60
    WHERE CURRENT OF c_emps;
END LOOP;

The WHERE CURRENT OF clause directly uses ROWID, making the update faster by skipping index lookups.
The cursor inherently returns ROWID values when used with FOR UPDATE.

Using WHERE CURRENT OF with DELETE

FOR r_emps IN c_emps LOOP
    DELETE FROM employees WHERE CURRENT OF c_emps;
END LOOP;

Limitations of WHERE CURRENT OF

Cannot be used with joins: If a cursor's query involves multiple tables, the ROWID reference becomes ambiguous.
Cannot be used with aggregate functions: The ROWID column is not included in grouped result sets.

Alternative Approach for Joins

DECLARE CURSOR c_emps IS
    SELECT e.ROWID, e.salary FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.department_id = 30 FOR UPDATE;

FOR r_emps IN c_emps LOOP
    UPDATE employees
    SET salary = salary + 60
    WHERE ROWID = r_emps.ROWID;
END LOOP;

Cursors in PL/SQL : 4

Table of contents