What is a Cursor in PL/SQL

🔹 What is a Cursor in PL/SQL?

In PL/SQL, a cursor is a pointer that allows you to process query results one row at a time.

When you execute a SELECT statement, Oracle stores the result set in memory.

A cursor gives you controlled access to those rows.

🔹 Why Do We Need a Cursor?

A normal SELECT INTO works only for a single row. But what if your query returns multiple rows?

👉 That’s where cursors are used.

Use cases:

∗ Loop through employee records

∗ Process multiple rows one by one

∗ Apply business logic per row

Types of Cursors in PL/SQL

1 Implicit Cursor

∗ Created automatically by Oracle ∗ Used for:

∗ INSERT

∗ UPDATE

∗ DELETE

∗ SELECT INTO

Example:

BEGIN
  UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;

2️⃣ Explicit Cursor

∗ Created by the developer ∗ Used when query returns multiple rows

Example:

DECLARE
  CURSOR emp_cur IS
    SELECT emp_id, emp_name FROM employees;
  v_id employees.emp_id%TYPE;
  v_name employees.emp_name%TYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
  END LOOP;
  CLOSE emp_cur;
END;

🔹 Cursor Life Cycle (Very Important for Interviews)

1 DECLARE – Define the cursor

2 OPEN – Execute the query

3 FETCH – Retrieve rows one by one

4 CLOSE – Release memory

Remember: Open → Fetch → Close

Cursor Attributes

| ----------- | ------------------------ |
| `%FOUND`    | TRUE if row fetched      |
| `%NOTFOUND` | TRUE if no row           |
| `%ROWCOUNT` | Number of rows processed |
| `%ISOPEN`   | Cursor open or not       |```

---

## 🔹 Cursor FOR Loop (Best Practice)

No need to `OPEN`, `FETCH`, or `CLOSE` manually 👇

```sql
BEGIN
  FOR rec IN (SELECT emp_id, emp_name FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.emp_id || ' ' || rec.emp_name);
  END LOOP;
END;

✔ Cleaner

✔ Less error-prone

✔ Interview favorite

🔹 Cursor vs SELECT INTO

| ---------------------- | -------------- |
| Single row only        | Multiple rows  |
| Simpler                | More control   |
| Raises error if >1 row | Safe for loops |```

---

## 🔹 Interview One-Liner

> **A cursor is a PL/SQL mechanism that allows row-by-row processing of query results stored in memory.**
---