Cursor FOR Loop FAQS

1. What is a Cursor FOR Loop in Oracle?

A Cursor FOR Loop is a simplified way to process query results row by row without explicitly declaring, opening, fetching, or closing a cursor.

Oracle automatically:

·        Declares the record variable

·        Opens the cursor

·        Fetches rows

·        Closes the cursor

It is cleaner and safer than manual cursor handling.

2. What is the syntax of a Cursor FOR Loop?

Method 1: Using a SELECT statement directly

BEGIN

   FOR rec IN (SELECT employee_id, first_name

               FROM employees

               WHERE department_id = 10)

   LOOP

      DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);

   END LOOP;

END;

Method 2: Using a declared cursor

DECLARE

   CURSOR emp_cursor IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = 10;

BEGIN

   FOR rec IN emp_cursor

   LOOP

      DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);

   END LOOP;

END;

3. How does Cursor FOR Loop work internally?

Internally, Oracle performs:

1.    OPEN cursor

2.    FETCH each row

3.    EXIT when no rows

4.    CLOSE cursor

All automatically.

4. Do we need OPEN, FETCH, and CLOSE statements?

❌ No.

Cursor FOR Loop automatically handles:

·        Opening

·        Fetching

·        Closing

That’s why it is less error-prone.

5. What type is the loop variable?

The loop variable:

·        Is automatically declared

·        Is a record type

·        Has fields matching the SELECT columns

Example:

rec.employee_id

rec.first_name

6. Can we use parameters in a Cursor FOR Loop?

Yes, if using a parameterized cursor.

Example:

DECLARE

   CURSOR emp_cursor (p_dept NUMBER) IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept;

BEGIN

   FOR rec IN emp_cursor(20)

   LOOP

      DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);

   END LOOP;

END;

7. What are the advantages of Cursor FOR Loop?

✔ Less code
✔ No risk of forgetting CLOSE
✔ No infinite loop mistakes
✔ Automatically handles exceptions for fetch
✔ Cleaner and readable

8. What are the limitations?

·        Less control compared to explicit cursor

·        Cannot manually fetch partial records

·        Cannot use cursor attributes like %ROWCOUNT directly inside the loop (only through cursor if declared separately)

9. When should you use Cursor FOR Loop?

Use it when:

·        Processing multiple rows

·        No need for complex cursor control

·        Simpler row-by-row logic

·        Writing clean PL/SQL programs

10. What is the difference between Cursor FOR Loop and Explicit Cursor?

Feature	Cursor FOR Loop	Explicit Cursor
OPEN required	No	Yes
FETCH required	No	Yes
CLOSE required	No	Yes
Code length	Short	Longer
Error-prone	Less	More
Control level	Moderate	High

11. Can Cursor FOR Loop be used in procedures and triggers?

Yes, it can be used in:

·        Procedures

·        Functions

·        Packages

·        Triggers

However, avoid heavy row-by-row processing for performance reasons.

12. Is Cursor FOR Loop better than explicit cursor?

For most use cases → Yes

But explicit cursor is better when:

·        You need manual control

·        You want to fetch specific number of rows

·        You need cursor attributes outside loop

·        You need complex fetch logic

13. What is an implicit cursor FOR loop?

When you directly use a SELECT statement inside the loop:

FOR rec IN (SELECT * FROM employees)

This is often called an implicit cursor FOR loop because no cursor is declared.