A Cursor is a pointer to the result set of a SQL query. It allows PL/SQL to retrieve and process one row at a time from the query result.

Cursors are mainly used when a query returns multiple rows.

Types of Cursors

1. Implicit Cursor
2. Explicit Cursor

1. Implicit Cursor

• Created automatically by Oracle.
• Used for SQL statements that return one row or affect rows (INSERT, UPDATE, DELETE, SELECT INTO).

Example

BEGIN
   UPDATE EMPLOYEE
   SET SALARY = SALARY + 5000
   WHERE EMP_ID = 101;

   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' Row Updated');
END;
/

Output

1 Row Updated

2. Explicit Cursor

• Created by the programmer.
• Used when a query returns multiple rows.
• The programmer controls opening, fetching, and closing the cursor.

Steps to Use an Explicit Cursor

1. Declare the cursor.
2. Open the cursor.
3. Fetch rows from the cursor.
4. Close the cursor.

Syntax

DECLARE
   CURSOR cursor_name IS
      SELECT column_list
      FROM table_name;

BEGIN
   OPEN cursor_name;

   LOOP
      FETCH cursor_name INTO variables;

      EXIT WHEN cursor_name%NOTFOUND;

      -- Process the row
   END LOOP;

   CLOSE cursor_name;
END;
/

Example: Explicit Cursor

Suppose the EMPLOYEE table contains:

DECLARE
   CURSOR c_emp IS
      SELECT EMP_ID, EMP_NAME, SALARY
      FROM EMPLOYEE;

   v_id EMPLOYEE.EMP_ID%TYPE;
   v_name EMPLOYEE.EMP_NAME%TYPE;
   v_salary EMPLOYEE.SALARY%TYPE;

BEGIN
   OPEN c_emp;

   LOOP
      FETCH c_emp INTO v_id, v_name, v_salary;

      EXIT WHEN c_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(
         v_id || ' ' || v_name || ' ' || v_salary
      );
   END LOOP;

   CLOSE c_emp;
END;
/

Output

101 Amit 25000
102 Rahul 30000
103 Neha 28000

Cursor FOR Loop (Simplest Method)

Oracle automatically opens, fetches, and closes the cursor.

DECLARE
   CURSOR c_emp IS
      SELECT EMP_ID, EMP_NAME
      FROM EMPLOYEE;
BEGIN
   FOR emp_rec IN c_emp LOOP
      DBMS_OUTPUT.PUT_LINE(
         emp_rec.EMP_ID || ' ' || emp_rec.EMP_NAME
      );
   END LOOP;
END;
/

Cursor Attributes

Example of Cursor Attributes

DECLARE
   CURSOR c_emp IS
      SELECT * FROM EMPLOYEE;

   emp_rec EMPLOYEE%ROWTYPE;
BEGIN
   OPEN c_emp;

   LOOP
      FETCH c_emp INTO emp_rec;

      EXIT WHEN c_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(
         'Rows fetched: ' || c_emp%ROWCOUNT
      );
   END LOOP;

   CLOSE c_emp;
END;
/

Advantages of Cursors

• Processes records one row at a time.
• Handles multiple-row query results.
• Gives greater control over data retrieval.
• Makes row-by-row processing easy.
• Supports complex business logic.

Implicit vs Explicit Cursor

Interview/Exam Questions

Q1. What is a cursor in PL/SQL?
Answer: A cursor is a pointer to the result set of a SQL query that allows row-by-row processing.

Q2. What are the two types of cursors?
Answer: Implicit Cursor and Explicit Cursor.

Q3. What are the four steps of an explicit cursor?
Answer:

1. Declare
2. Open
3. Fetch
4. Close

Q4. Which cursor attribute returns the number of rows fetched?
Answer: %ROWCOUNT

Q5. Which cursor attribute checks whether a cursor is open?
Answer: %ISOPEN