Cursors in PL/SQL

Sujan HaldarSujan Haldar
6 min read

Table of contents

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

  1. 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.

  2. 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;
0
Subscribe to my newsletter

Read articles from Sujan Haldar directly inside your inbox. Subscribe to the newsletter, and don't miss out.

PL/SQL

Written by

Sujan Haldar
Sujan Haldar

Passionate Computer Science graduate with a strong foundation in software development, eager to contribute to innovative projects and make an impact in the tech industry. Proficient in JAVA , JavaScript with hands-on experience in Node JS.