Tuesday, 1 July 2025

All about Cursors in Oracle PL/SQL

 In this blog post, we will see how to use cursors to fetch multiple rows from a query result set

Implicit Cursor:

Here is the example for the Implicit Cursor

BEGIN

   FOR rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 100) LOOP

      DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name);

   END LOOP;

END;


Explicit Cursor:

Here is the example for the Implicit Cursor


DECLARE

   CURSOR c_emp IS SELECT employee_id, first_name FROM employees;

   v_emp_id employees.employee_id%TYPE;

   v_name employees.first_name%TYPE;

BEGIN

   OPEN c_emp;

   LOOP

      FETCH c_emp INTO v_emp_id, v_name;

      EXIT WHEN c_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_name);

   END LOOP;

   CLOSE c_emp;

END;