Oracle Cursor FOR LOOP Faqs With Examples

1. What is a Cursor FOR LOOP?

A Cursor FOR LOOP executes a block of code once for every row returned by a cursor or query.

Basic syntax:

FOR record_name IN cursor_name
LOOP
    statements;
END LOOP;

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;
BEGIN
    FOR emp IN emp_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' ||
            emp.first_name || ' - ' ||
            emp.salary
        );
    END LOOP;
END;
/

2. Why use a Cursor FOR LOOP?

It simplifies cursor processing. With a traditional explicit cursor, you need DECLARE, OPEN, FETCH, LOOP, EXIT, and CLOSE. With a Cursor FOR LOOP, Oracle automatically handles the cursor lifecycle.

FOR emp IN emp_cursor
LOOP
    ...
END LOOP;

Main advantage: No explicit OPEN, FETCH, or CLOSE is required.

3. What happens internally in a Cursor FOR LOOP?

Conceptually, PL/SQL performs this sequence:

Open cursor
    ↓
Fetch row 1
    ↓
Execute loop body
    ↓
Fetch row 2
    ↓
Execute loop body
    ↓
Fetch row 3
    ↓
Execute loop body
    ↓
No more rows
    ↓
Close cursor

4. Do we need to OPEN the cursor?

No. The Cursor FOR LOOP automatically opens the cursor.

5. Do we need to FETCH the cursor?

No. The Cursor FOR LOOP automatically fetches each row.

6. Do we need to CLOSE the cursor?

No. Oracle automatically closes the cursor after the loop finishes.

7. What is the syntax using a declared cursor?

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    FOR emp IN emp_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

Here, emp_cursor is the explicit cursor and emp is the loop record.

8. What is the loop record?

The variable after FOR is called the loop record. You can access the columns using emp.employee_id, emp.first_name, and emp.salary.

9. Do we need to declare the loop record?

No. PL/SQL automatically creates the loop record.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

10. Can we use a query directly in a Cursor FOR LOOP?

Yes. This is called an implicit cursor FOR LOOP.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' ||
            emp.first_name
        );
    END LOOP;
END;
/

11. Named cursor vs Inline query

Named Cursor Inline Query
Cursor declared separately Query written directly
Can be reused Usually used for one loop
More structured Shorter and convenient

12. Can a Cursor FOR LOOP process multiple rows?

Yes. If 100 rows are returned, the loop executes 100 times.

13. What if the query returns no rows?

The loop simply executes zero times. A Cursor FOR LOOP with zero rows does not raise NO_DATA_FOUND.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
        WHERE employee_id = 999999
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Loop completed');
END;
/

14. Can we use WHERE conditions?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE salary > 50000
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name || ' earns ' ||
            emp.salary
        );
    END LOOP;
END;
/

15. Can we use ORDER BY?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
        ORDER BY salary DESC
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name || ' - ' || emp.salary
        );
    END LOOP;
END;
/

16. Can we use joins?

Yes.

BEGIN
    FOR emp IN (
        SELECT e.employee_id,
               e.first_name,
               d.department_name
        FROM employees e
        JOIN departments d
          ON e.department_id = d.department_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name || ' - ' ||
            emp.department_name
        );
    END LOOP;
END;
/

17. Can we use aggregate functions?

Yes.

BEGIN
    FOR rec IN (
        SELECT department_id,
               COUNT(*) AS employee_count,
               AVG(salary) AS average_salary
        FROM employees
        GROUP BY department_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Department: ' || rec.department_id ||
            ', Employees: ' || rec.employee_count ||
            ', Average: ' || rec.average_salary
        );
    END LOOP;
END;
/

18. Can we use IF inside a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
    )
    LOOP
        IF emp.salary >= 100000 THEN
            DBMS_OUTPUT.PUT_LINE(
                emp.first_name || ' - High salary'
            );
        ELSE
            DBMS_OUTPUT.PUT_LINE(
                emp.first_name || ' - Regular salary'
            );
        END IF;
    END LOOP;
END;
/

19. Can we use UPDATE inside a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
    )
    LOOP
        IF emp.salary < 50000 THEN
            UPDATE employees
            SET salary = salary + 5000
            WHERE employee_id = emp.employee_id;
        END IF;
    END LOOP;

```
COMMIT;
```

END;
/

20. Can we use DELETE inside a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id
        FROM employees
        WHERE salary < 20000
    )
    LOOP
        DELETE FROM employees
        WHERE employee_id = emp.employee_id;
    END LOOP;

```
COMMIT;
```

END;
/

21. What is FOR UPDATE in a Cursor FOR LOOP?

FOR UPDATE can be used when the selected rows are going to be updated or deleted.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = 10
        FOR UPDATE;
BEGIN
    FOR emp IN emp_cursor
    LOOP
        IF emp.salary < 50000 THEN
            UPDATE employees
            SET salary = salary + 5000
            WHERE CURRENT OF emp_cursor;
        END IF;
    END LOOP;

    COMMIT;
END;
/

22. What is WHERE CURRENT OF?

WHERE CURRENT OF refers to the current row selected by a FOR UPDATE cursor.

UPDATE employees
SET salary = salary + 1000
WHERE CURRENT OF emp_cursor;

Remember:

FOR UPDATE
     ↓
Cursor identifies/locks rows for update
     ↓
WHERE CURRENT OF
     ↓
Update/delete current row

23. Can a parameterized cursor be used with a Cursor FOR LOOP?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' ||
            emp.first_name
        );
    END LOOP;
END;
/

24. Can we pass different parameters?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;

```
FOR emp IN emp_cursor(20)
LOOP
    DBMS_OUTPUT.PUT_LINE(emp.first_name);
END LOOP;
```

END;
/

25. Can we use %ROWCOUNT with a Cursor FOR LOOP?

The loop itself automatically manages fetching, so you generally do not need cursor attributes for basic iteration. If you need a count of processed rows, maintain your own counter.

DECLARE
    v_count NUMBER := 0;
BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
    )
    LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(
        'Total rows processed: ' || v_count
    );
END;
/

26. Can we use EXIT inside a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
        ORDER BY employee_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);

```
    EXIT WHEN emp.employee_id = 105;
END LOOP;
```

END;
/

27. Can we use CONTINUE inside a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name, salary
        FROM employees
    )
    LOOP
        CONTINUE WHEN emp.salary < 30000;

```
    DBMS_OUTPUT.PUT_LINE(
        emp.first_name || ' - ' || emp.salary
    );
END LOOP;
```

END;
/

28. EXIT vs CONTINUE

EXIT CONTINUE
Terminates the loop Skips the current iteration
Stops processing completely Moves to the next row

29. Can we use an exception handler?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

30. What happens if an error occurs inside the loop?

Control transfers to the applicable exception handler. For a Cursor FOR LOOP, the cursor is automatically managed by PL/SQL.

31. Do we need to explicitly CLOSE the cursor in an exception?

No. For a Cursor FOR LOOP, no manual close is required.

32. Cursor FOR LOOP vs Traditional Explicit Cursor

Traditional explicit cursor:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;

```
v_id   employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
```

BEGIN
OPEN emp_cursor;

```
LOOP
    FETCH emp_cursor INTO v_id, v_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

CLOSE emp_cursor;
```

END;
/

Cursor FOR LOOP:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    FOR emp IN emp_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

Key difference: the Cursor FOR LOOP eliminates manual OPEN, FETCH, EXIT WHEN %NOTFOUND, and CLOSE.

33. Cursor FOR LOOP vs Implicit Cursor

Implicit cursor is Oracle automatically managing a SQL statement, while the Cursor FOR LOOP processes rows using automatic cursor handling for iteration.

34. What happens if the query returns 1,000 rows?

The loop body executes once per row, so it runs 1,000 times.

35. Can the loop record be modified?

The loop record should be treated as a read-only representation of the current fetched row. To modify database data, use SQL such as UPDATE or DELETE.

36. Can we use aliases in a Cursor FOR LOOP?

Yes.

BEGIN
    FOR emp IN (
        SELECT employee_id AS id,
               first_name AS name,
               salary AS pay
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.id || ' - ' ||
            emp.name || ' - ' ||
            emp.pay
        );
    END LOOP;
END;
/

37. Can we use expressions in the query?

Yes.

BEGIN
    FOR emp IN (
        SELECT first_name,
               salary,
               salary * 12 AS annual_salary
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.first_name ||
            ' Annual Salary: ' ||
            emp.annual_salary
        );
    END LOOP;
END;
/

38. Can we use nested Cursor FOR LOOPS?

Yes.

BEGIN
    FOR dept IN (
        SELECT department_id, department_name
        FROM departments
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Department: ' || dept.department_name
        );

```
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = dept.department_id
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            '  Employee: ' || emp.first_name
        );
    END LOOP;
END LOOP;
```

END;
/

39. Advantages of Cursor FOR LOOP

  1. Simple syntax
  2. No explicit OPEN
  3. No explicit FETCH
  4. No explicit CLOSE
  5. No need to declare a loop record
  6. Automatically processes multiple rows
  7. Less code
  8. Lower chance of cursor-management mistakes
  9. Works with named cursors and inline queries
  10. Supports parameterized cursors

40. Disadvantages

For many straightforward row-processing tasks, there are not significant disadvantages compared with manually managed explicit cursors. However, a manually controlled explicit cursor may be preferable when you specifically need control over when the cursor is opened, fetched, or closed. For large-scale data manipulation, a set-based SQL statement is often more efficient than row-by-row processing.

41. Frequently Asked Questions

Question Answer
What is a Cursor FOR LOOP? A PL/SQL loop that processes each row returned by a cursor or query.
Does it automatically open the cursor? Yes.
Does it automatically fetch rows? Yes.
Does it automatically close the cursor? Yes.
Do we need EXIT WHEN cursor%NOTFOUND? No.
Do we need to declare a loop variable? No.
Can we use an inline SELECT? Yes.
What happens if the query returns zero rows? The loop body executes zero times.
Can a Cursor FOR LOOP process multiple rows? Yes.
Can we use parameters? Yes, with a parameterized cursor.

Important Exam Points

  1. Cursor FOR LOOP processes rows one at a time.
  2. It automatically performs OPEN → FETCH → CLOSE.
  3. No explicit OPEN is required.
  4. No explicit FETCH is required.
  5. No explicit CLOSE is required.
  6. No EXIT WHEN %NOTFOUND is required.
  7. The loop variable is automatically created as a record.
  8. It can use a named cursor.
  9. It can use an inline query.
  10. Zero rows → loop body executes zero times.
  11. Multiple rows → loop body executes once per row.
  12. FOR UPDATE and WHERE CURRENT OF can be used for appropriate update scenarios.
  13. EXIT terminates the loop.
  14. CONTINUE skips the current iteration.
  15. Parameterized cursors can be used with Cursor FOR LOOP.
  16. For large set-based changes, prefer a single SQL statement when possible.

Quick Revision

CURSOR FOR LOOP
        |
  +-----+-----+
  |           |
Named     Inline Query
Cursor    (SELECT ...)
  |           |
  +-----+-----+
        |
    Automatic
        |
OPEN → FETCH → CLOSE
        |
   Process each row

One-line memory trick: Cursor FOR LOOP = process every returned row without manually OPENing, FETCHing, or CLOSEing the cursor.

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)