Oracle Implicit Cursor Faqs With Examples

1. What is an Implicit Cursor?

An implicit cursor is a cursor that Oracle automatically creates and manages whenever a SQL statement is executed.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;


DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);


END;
/

Oracle automatically creates and manages the cursor for the UPDATE.

2. When does Oracle create an Implicit Cursor?

Oracle creates an implicit cursor when you execute SQL statements such as INSERT, UPDATE, DELETE, MERGE, and SELECT INTO.

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

3. Do we need to declare an Implicit Cursor?

No. You do not write CURSOR my_cursor IS ... for an implicit cursor. Oracle manages it automatically.

4. Do we need to OPEN an Implicit Cursor?

No. Oracle automatically opens and manages the implicit cursor when the SQL statement executes.

5. Do we need to FETCH an Implicit Cursor?

No. Oracle handles the processing automatically.

6. Do we need to CLOSE an Implicit Cursor?

No. Oracle automatically closes the implicit cursor after the SQL statement completes.

7. What is the name of the Implicit Cursor?

The implicit cursor is accessed using the special cursor name SQL.

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

8. What are Implicit Cursor Attributes?

Attribute	Meaning
SQL%FOUND	Last SQL statement affected or found a row
SQL%NOTFOUND	Last SQL statement affected or found no row
SQL%ROWCOUNT	Number of rows affected
SQL%ISOPEN	Whether the cursor is open

9. What is SQL%FOUND?

SQL%FOUND tells whether the most recently executed SQL statement affected or returned at least one row, as applicable.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee updated');
END IF;


END;
/

10. What is SQL%NOTFOUND?

SQL%NOTFOUND becomes TRUE when the most recent SQL operation did not affect or return a row, as applicable.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 9999;


IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee updated');
END IF;


END;
/

11. What is SQL%ROWCOUNT?

SQL%ROWCOUNT tells how many rows were affected by the most recent DML statement.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;


DBMS_OUTPUT.PUT_LINE(
    'Rows updated: ' || SQL%ROWCOUNT
);


END;
/

12. What is SQL%ISOPEN?

SQL%ISOPEN tells whether the implicit SQL cursor is open. For an implicit cursor, Oracle manages its lifecycle, and after the statement completes, SQL%ISOPEN is always FALSE.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
ELSE
    DBMS_OUTPUT.PUT_LINE('Cursor is closed');
END IF;


END;
/

Exam point: For an implicit cursor, SQL%ISOPEN is always FALSE after the statement completes.

13. Implicit Cursor with INSERT

BEGIN
    INSERT INTO employees
    (employee_id, first_name, salary)
    VALUES
    (101, 'Ravi', 50000);


DBMS_OUTPUT.PUT_LINE(
    'Rows inserted: ' || SQL%ROWCOUNT
);


END;
/

14. Implicit Cursor with UPDATE

BEGIN
    UPDATE employees
    SET salary = 60000
    WHERE employee_id = 101;


DBMS_OUTPUT.PUT_LINE(
    'Rows updated: ' || SQL%ROWCOUNT
);


END;
/

15. Implicit Cursor with DELETE

BEGIN
    DELETE FROM employees
    WHERE employee_id = 101;


DBMS_OUTPUT.PUT_LINE(
    'Rows deleted: ' || SQL%ROWCOUNT
);


END;
/

16. Implicit Cursor with MERGE

BEGIN
    MERGE INTO employees e
    USING new_employees n
    ON (e.employee_id = n.employee_id)
    WHEN MATCHED THEN
        UPDATE SET e.salary = n.salary
    WHEN NOT MATCHED THEN
        INSERT (employee_id, first_name, salary)
        VALUES (n.employee_id, n.first_name, n.salary);


DBMS_OUTPUT.PUT_LINE(
    'Rows affected: ' || SQL%ROWCOUNT
);


END;
/

17. Implicit Cursor with SELECT INTO

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE employee_id = 101;


DBMS_OUTPUT.PUT_LINE(v_name);


END;
/

Oracle automatically manages the cursor for the SELECT INTO.

18. What happens if SELECT INTO finds no row?

Oracle raises the predefined exception NO_DATA_FOUND.

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE employee_id = 9999;


DBMS_OUTPUT.PUT_LINE(v_name);


EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

19. What happens if SELECT INTO finds multiple rows?

Oracle raises TOO_MANY_ROWS.

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE department_id = 10;
END;
/

20. How does SQL%ROWCOUNT work with DELETE?

BEGIN
    DELETE FROM employees
    WHERE department_id = 20;


DBMS_OUTPUT.PUT_LINE(
    'Deleted: ' || SQL%ROWCOUNT
);


END;
/

21. What happens to SQL%ROWCOUNT after another SQL statement?

Implicit cursor attributes refer to the most recently executed SQL statement.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;


DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

DELETE FROM employees
WHERE employee_id = 101;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);


END;
/

The first SQL%ROWCOUNT belongs to the UPDATE, and the second belongs to the DELETE.

22. Implicit Cursor Example with IF

BEGIN
    UPDATE employees
    SET salary = salary + 5000
    WHERE employee_id = 101;


IF SQL%ROWCOUNT = 1 THEN
    DBMS_OUTPUT.PUT_LINE('One employee updated');
ELSIF SQL%ROWCOUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No employee found');
END IF;


END;
/

23. Implicit Cursor Example with ROWCOUNT

DECLARE
    v_count NUMBER;
BEGIN
    DELETE FROM employees
    WHERE department_id = 30;


v_count := SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE(
    'Number of rows deleted = ' || v_count
);


END;
/

24. Why save SQL%ROWCOUNT in a variable?

Because another SQL statement can change the implicit cursor information.

DECLARE
    v_count NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;

    v_count := SQL%ROWCOUNT;

    DBMS_OUTPUT.PUT_LINE(
        'Rows updated = ' || v_count
    );
END;
/

25. Implicit Cursor vs Explicit Cursor

Implicit Cursor	Explicit Cursor
Created automatically	Declared by programmer
Oracle manages it	Programmer controls it
No explicit OPEN	Usually explicitly opened
No explicit FETCH	Can explicitly fetch
No explicit CLOSE	Explicitly closed when manually managed
Uses SQL%...	Uses cursor_name%...

26. Implicit Cursor vs Cursor FOR LOOP

An implicit cursor is automatically managed by Oracle for SQL statements, while a Cursor FOR LOOP automatically manages OPEN, FETCH, and CLOSE for row-by-row processing.

27. Do we use OPEN, FETCH, and CLOSE with an Implicit Cursor?

No. You do not normally write OPEN SQL, FETCH SQL, or CLOSE SQL. Oracle handles these operations.

28. What is the main advantage of an Implicit Cursor?

It is simple and automatic.

29. What is the disadvantage?

Implicit cursors provide less manual control over multi-row processing than explicit cursors.

30. Complete Example

DECLARE
    v_count NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary + 2000
    WHERE department_id = 10;


v_count := SQL%ROWCOUNT;

IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(
        'Employees updated: ' || v_count
    );
ELSE
    DBMS_OUTPUT.PUT_LINE(
        'No employees updated'
    );
END IF;


END;
/

Step-by-step:

UPDATE executed
      ↓
Oracle creates implicit cursor
      ↓
Oracle processes the statement
      ↓
SQL%ROWCOUNT gives affected rows
      ↓
SQL%FOUND checks whether rows were affected
      ↓
Oracle manages the cursor automatically

31. Frequently Asked Questions

Question	Answer
What is an implicit cursor?	A cursor automatically created and managed by Oracle for SQL statements.
Who manages an implicit cursor?	Oracle.
Do we declare an implicit cursor?	No.
Do we explicitly open an implicit cursor?	No.
Do we explicitly fetch from an implicit cursor?	No.
Do we explicitly close an implicit cursor?	No.
What is the implicit cursor name?	SQL
What are the implicit cursor attributes?	SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Which attribute tells how many rows were affected?	SQL%ROWCOUNT
Which attribute tells whether a row was affected or found?	SQL%FOUND
Which attribute tells that no row was affected or found?	SQL%NOTFOUND
What is the value of SQL%ISOPEN after the statement completes?	FALSE

Important Exam Points

1. Implicit cursor = automatically managed by Oracle.
2. The implicit cursor is referenced using SQL.
3. No explicit DECLARE, OPEN, FETCH, or CLOSE is required.
4. Commonly associated with INSERT, UPDATE, DELETE, MERGE, and SELECT INTO.
5. SQL%FOUND means a row was affected or found.
6. SQL%NOTFOUND means no row was affected or found.
7. SQL%ROWCOUNT means the number of rows affected or fetched, as applicable.
8. SQL%ISOPEN is FALSE after the statement completes.
9. SELECT INTO generally expects exactly one row.
10. No row from SELECT INTO causes NO_DATA_FOUND.
11. Multiple rows from SELECT INTO cause TOO_MANY_ROWS.
12. Cursor attributes refer to the most recently executed relevant SQL statement.

Easy Memory Trick

IMPLICIT CURSOR
       ↓
Oracle manages it
       ↓
No DECLARE
No OPEN
No FETCH
No CLOSE
       ↓
Use SQL% attributes

FOUND     → Row found
NOTFOUND  → No row
ROWCOUNT  → Number of rows
ISOPEN    → Cursor status

Implicit Cursor = Oracle manages the cursor; you mainly use SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN to check what happened.

Oracle Explicit Cursor Faqs With Examples

1. What is an Explicit Cursor?

An explicit cursor is a cursor that is declared and controlled by the programmer in PL/SQL. It is mainly used when a query returns multiple rows and you want to process those rows one by one.

Basic flow:

DECLARE
   ↓
OPEN
   ↓
FETCH
   ↓
PROCESS
   ↓
CLOSE

Example:

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_id || ' - ' || v_name
    );
END LOOP;

CLOSE emp_cursor;


END;
/

2. Why do we use Explicit Cursors?

Explicit cursors are useful when you need to process multiple rows one by one, apply different logic to individual rows, perform calculations for each row, update or process data based on each fetched record, or have more control over the cursor lifecycle.

3. What are the steps of an Explicit Cursor?

There are traditionally four steps: DECLARE, OPEN, FETCH, and CLOSE.

Easy memory trick:

DOFC = Declare → Open → Fetch → Close

4. How do you declare an Explicit Cursor?

Syntax:

CURSOR cursor_name IS
    SELECT_statement;

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;
BEGIN
    NULL;
END;
/

5. What is the OPEN operation?

OPEN activates the explicit cursor and makes its result set available for fetching.

OPEN emp_cursor;

6. What is FETCH?

FETCH retrieves the next row from the cursor result set into variables or a record.

FETCH emp_cursor
INTO v_id, v_name, v_salary;

7. What is CLOSE?

CLOSE closes the cursor after processing is complete.

CLOSE emp_cursor;

8. Complete Explicit Cursor Example

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;


v_id     employees.employee_id%TYPE;
v_name   employees.first_name%TYPE;
v_salary employees.salary%TYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor
    INTO v_id, v_name, v_salary;

    EXIT WHEN emp_cursor%NOTFOUND;

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

CLOSE emp_cursor;


END;
/

Execution flow:

DECLARE cursor
      ↓
OPEN cursor
      ↓
FETCH row 1
      ↓
Process row 1
      ↓
FETCH row 2
      ↓
Process row 2
      ↓
...
      ↓
%NOTFOUND = TRUE
      ↓
EXIT LOOP
      ↓
CLOSE cursor

9. What are Explicit Cursor Attributes?

Oracle provides four important attributes:

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ROWCOUNT
cursor_name%ISOPEN

10. What is %FOUND?

%FOUND tells whether the most recent fetch successfully retrieved a row.

Example:

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;


FETCH emp_cursor INTO v_id, v_name;

IF emp_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Row found');
END IF;

CLOSE emp_cursor;


END;
/

11. What is %NOTFOUND?

%NOTFOUND becomes TRUE when the most recent FETCH does not retrieve a row. It is commonly used to stop a cursor loop.

LOOP
    FETCH emp_cursor INTO v_id, v_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

Important: %NOTFOUND is commonly used with EXIT WHEN in explicit cursor loops.

12. What is %ROWCOUNT?

%ROWCOUNT tells how many rows have been fetched so far.

FETCH emp_cursor INTO v_id, v_name;

DBMS_OUTPUT.PUT_LINE(
    'Rows fetched: ' || emp_cursor%ROWCOUNT
);

After the first fetch, the value is 1; after the second fetch, it is 2; and so on.

13. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

OPEN emp_cursor;

IF emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;

CLOSE emp_cursor;

After CLOSE, emp_cursor%ISOPEN = FALSE.

14. Explicit Cursor Attributes — Summary

Attribute	Meaning
%FOUND	Last fetch successfully found a row
%NOTFOUND	Last fetch did not find a row
%ROWCOUNT	Number of rows fetched so far
%ISOPEN	Whether cursor is open

15. Why is %NOTFOUND important?

When there are no more rows, %NOTFOUND becomes TRUE and the loop exits.

FETCH
 ↓
No row
 ↓
%NOTFOUND = TRUE
 ↓
EXIT

16. What is the correct position of %NOTFOUND?

The common pattern is to check it after the FETCH.

LOOP
    FETCH emp_cursor INTO v_id, v_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    -- Process fetched row
END LOOP;

17. Can an Explicit Cursor return multiple rows?

Yes. This is one of its main uses.

18. Can an Explicit Cursor return zero rows?

Yes. If the query returns no rows, the cursor can still be opened, and the first fetch will indicate that no row was returned.

19. Explicit Cursor with WHERE Condition

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

20. What is a Parameterized Explicit Cursor?

A parameterized cursor accepts parameters when it is opened or used.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;

    v_id   employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN emp_cursor(10);

    LOOP
        FETCH emp_cursor INTO v_id, v_name;
        EXIT WHEN emp_cursor%NOTFOUND;

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

    CLOSE emp_cursor;
END;
/

21. Why use a Parameterized Cursor?

It allows the same cursor definition to be reused with different values.

22. Can we create multiple Explicit Cursors?

Yes. Each cursor can have its own query and lifecycle.

23. What is a Cursor FOR LOOP?

A Cursor FOR LOOP is a convenient way to process cursor rows. Oracle/PLSQL automatically handles OPEN, FETCH, and CLOSE for the 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.employee_id || ' - ' || emp.first_name
        );
    END LOOP;
END;
/

24. Do we need OPEN, FETCH, and CLOSE with a Cursor FOR LOOP?

No. The Cursor FOR LOOP automatically handles the cursor lifecycle.

25. Explicit Cursor vs Cursor FOR LOOP

Manual Explicit Cursor	Cursor FOR LOOP
OPEN emp_cursor	No OPEN needed
FETCH emp_cursor INTO ...	No FETCH needed
EXIT WHEN emp_cursor%NOTFOUND	No EXIT WHEN needed
CLOSE emp_cursor	No CLOSE needed

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

Yes. You do not even need to declare a named cursor.

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

27. What happens if you FETCH from a closed cursor?

Oracle raises a cursor-related error such as INVALID_CURSOR.

28. What happens if you OPEN an already open cursor?

An already open explicit cursor cannot be opened again without first closing it. This causes an error such as INVALID_CURSOR.

29. What happens if you CLOSE an already closed cursor?

Attempting to close an already closed cursor raises a cursor error such as INVALID_CURSOR.

30. What happens if the cursor query returns no rows?

The cursor can be opened, but the fetch indicates that no row was returned.

31. Explicit Cursor with Record

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_emp.employee_id || ' - ' ||
        v_emp.first_name || ' - ' ||
        v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

32. What is %ROWTYPE with a Cursor?

cursor_name%ROWTYPE creates a record that can hold one row returned by the cursor.

33. Can an Explicit Cursor be used for UPDATE?

Yes. You can fetch rows and then perform operations based on the fetched values.

34. What is FOR UPDATE in an Explicit Cursor?

FOR UPDATE can be used when you intend to update or delete the rows selected by the cursor.

35. What is WHERE CURRENT OF?

WHERE CURRENT OF allows you to update or delete the current row of a cursor declared with FOR UPDATE.

Easy memory trick:

FOR UPDATE
    ↓
Lock selected rows for update
    ↓
WHERE CURRENT OF
    ↓
Modify current cursor row

36. Explicit Cursor vs Implicit Cursor

Explicit Cursor	Implicit Cursor
Programmer declares it	Oracle creates it
Programmer controls lifecycle	Oracle manages lifecycle
Uses cursor_name%...	Uses SQL%...
Suitable for controlled multi-row processing	Convenient for single SQL statements

37. Explicit Cursor vs SELECT INTO

SELECT INTO: Suitable for single-row retrieval.

Explicit Cursor: Suitable for multiple rows and row-by-row processing.

38. Complete Example with All Four Steps

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        'ID: ' || v_emp.employee_id ||
        ', Name: ' || v_emp.first_name ||
        ', Salary: ' || v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

39. Frequently Asked Interview Questions

Question	Answer
What is an explicit cursor?	A cursor explicitly declared and controlled by the programmer to process rows one by one.
What are the four steps?	DECLARE → OPEN → FETCH → CLOSE
Which command opens a cursor?	OPEN cursor_name;
Which command retrieves a row?	FETCH cursor_name INTO variables;
Which command closes a cursor?	CLOSE cursor_name;
What does %FOUND mean?	The last fetch successfully retrieved a row.
What does %NOTFOUND mean?	The last fetch did not retrieve a row.
What does %ROWCOUNT mean?	Number of rows fetched so far.
What does %ISOPEN mean?	Whether the explicit cursor is currently open.
What happens if you fetch from a closed cursor?	An INVALID_CURSOR error occurs.

Important Exam Points

1. Explicit cursor = programmer-controlled cursor.
2. Mainly used for multiple-row processing.
3. Four traditional steps: DECLARE → OPEN → FETCH → CLOSE.
4. OPEN activates the cursor.
5. FETCH retrieves the next row.
6. CLOSE closes the cursor.
7. %FOUND means the last fetch found a row.
8. %NOTFOUND means the last fetch found no row.
9. %ROWCOUNT means number of rows fetched.
10. %ISOPEN means cursor open status.
11. cursor_name%ROWTYPE can represent one cursor row.
12. Parameterized cursors accept input values.
13. Cursor FOR loops automatically manage open, fetch, and close.
14. FOR UPDATE is used when selected rows need to be locked for update/delete processing.
15. WHERE CURRENT OF operates on the current row of a FOR UPDATE cursor.
16. An explicit cursor uses cursor_name%attribute, unlike an implicit cursor, which uses SQL%attribute.

One-Line Memory Trick

EXPLICIT CURSOR
      ↓
DECLARE
      ↓
OPEN
      ↓
FETCH
      ↓
%NOTFOUND?
   ↓       ↓
  NO      YES
  ↓        ↓
PROCESS   EXIT
           ↓
         CLOSE

Explicit Cursor = Programmer controls the cursor to process rows one by one.

Oracle Cursor Attributes FAQs with Examples

1. What are Cursor Attributes?

Cursor attributes are properties that tell you what happened during cursor processing.

For an implicit cursor:

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

For an explicit cursor:

emp_cursor%FOUND
emp_cursor%NOTFOUND
emp_cursor%ROWCOUNT
emp_cursor%ISOPEN

2. What are the four main Cursor Attributes?

Attribute	Meaning
%FOUND	Indicates whether a row was found or affected
%NOTFOUND	Indicates whether no row was found or affected
%ROWCOUNT	Number of rows affected or fetched
%ISOPEN	Indicates whether an explicit cursor is open

Memory trick: F-N-R-I = Found, Not Found, Row Count, Is Open

3. What is %FOUND?

%FOUND tells whether the most recent cursor operation successfully found or affected a row, as applicable.

Explicit cursor example:

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;
FETCH emp_cursor INTO v_id, v_name;
IF emp_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Row found');
END IF;
CLOSE emp_cursor;
END;
/

If the fetch retrieves a row, the output is:

Row found

4. What is SQL%FOUND?

For an implicit cursor, use SQL%FOUND.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee updated');
END IF;


END;
/

If employee 101 was updated, %FOUND is TRUE.

5. What is %NOTFOUND?

%NOTFOUND indicates that the most recent cursor operation did not find or affect a row, as applicable. It is particularly important when processing an explicit cursor.

Example:

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;
/

Flow:

FETCH
  ↓
Row found?
  ↓
YES → Process row
  ↓
FETCH again
  ↓
No row
  ↓
%NOTFOUND = TRUE
  ↓
EXIT

6. What is SQL%NOTFOUND?

For an implicit cursor, SQL%NOTFOUND can be used after DML to determine that no rows were affected.

Example:

BEGIN
    DELETE FROM employees
    WHERE employee_id = 9999;


IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee deleted');
END IF;


END;
/

If employee 9999 does not exist, the output is:

No employee deleted

7. What is %ROWCOUNT?

%ROWCOUNT tells the number of rows processed. For an explicit cursor, it gives the number of rows fetched so far.

Example:

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(
        'Rows fetched: ' || emp_cursor%ROWCOUNT
    );
END LOOP;

CLOSE emp_cursor;


END;
/

The values increase as rows are fetched:

Rows fetched: 1
Rows fetched: 2
Rows fetched: 3
...

8. What is SQL%ROWCOUNT?

For an implicit cursor, SQL%ROWCOUNT tells the number of rows affected by the most recent DML statement.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 2000
    WHERE department_id = 10;


DBMS_OUTPUT.PUT_LINE(
    'Rows updated: ' || SQL%ROWCOUNT
);


END;
/

If 10 employees are updated, the output is:

Rows updated: 10

9. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id
        FROM employees;
BEGIN
    OPEN emp_cursor;


IF emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;

CLOSE emp_cursor;


END;
/

Output:

Cursor is open

10. What is SQL%ISOPEN?

For an implicit cursor, Oracle manages the cursor automatically.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Open');
ELSE
    DBMS_OUTPUT.PUT_LINE('Closed');
END IF;


END;
/

After the statement completes, the implicit cursor is closed, so the result is:

Closed

Exam point: SQL%ISOPEN is always FALSE after the implicit SQL statement has completed.

11. Difference between implicit and explicit cursor attributes

Implicit Cursor	Explicit Cursor
SQL%FOUND	emp_cursor%FOUND
SQL%NOTFOUND	emp_cursor%NOTFOUND
SQL%ROWCOUNT	emp_cursor%ROWCOUNT
SQL%ISOPEN	emp_cursor%ISOPEN

12. Example of all four explicit cursor attributes

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;
IF emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;
LOOP
    FETCH emp_cursor INTO v_id, v_name;

    IF emp_cursor%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || emp_cursor%ROWCOUNT);
    END IF;

    EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
IF NOT emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is closed');
END IF;
END;
/

This demonstrates %ISOPEN, %FOUND, %ROWCOUNT, and %NOTFOUND.

13. Why is %NOTFOUND usually checked after FETCH?

Because the cursor needs to attempt a fetch before it can determine whether another row exists.

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

14. What happens to %ROWCOUNT after each FETCH?

For an explicit cursor:

First successful FETCH  → %ROWCOUNT = 1
Second successful FETCH → %ROWCOUNT = 2
Third successful FETCH  → %ROWCOUNT = 3

15. Example of %ROWCOUNT with INSERT

BEGIN
    INSERT INTO employees
    (employee_id, first_name, salary)
    VALUES
    (101, 'Ravi', 50000);
DBMS_OUTPUT.PUT_LINE(
    'Rows inserted: ' || SQL%ROWCOUNT
);
END;
/

Output:

Rows inserted: 1

16. Example of %ROWCOUNT with DELETE

BEGIN
    DELETE FROM employees
    WHERE department_id = 20;
DBMS_OUTPUT.PUT_LINE(
    'Rows deleted: ' || SQL%ROWCOUNT
);


END;
/

If 7 rows were deleted, the output is:

Rows deleted: 7

17. Example of %ROWCOUNT with UPDATE

BEGIN
    UPDATE employees
    SET salary = 60000
    WHERE department_id = 10;


IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated');
ELSE
    DBMS_OUTPUT.PUT_LINE('No employees updated');
END IF;


END;
/

18. Can %ROWCOUNT be zero?

Yes.

BEGIN
    DELETE FROM employees
    WHERE employee_id = 9999;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
/

If the employee does not exist, the output is:

0

19. Can %FOUND and %NOTFOUND both be TRUE?

No. For a given cursor state, they represent opposite conditions.

Conceptually:

%FOUND    = TRUE  → row found
%NOTFOUND = TRUE  → row not found

20. What happens to cursor attributes after another SQL statement?

Cursor attributes can be affected by subsequent SQL operations.

Best practice: save the value if you need it later.

DECLARE
    v_count NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;


v_count := SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE('Employees updated: ' || v_count);


END;
/

21. What happens if %NOTFOUND is used before the first FETCH?

For an explicit cursor, you should not use %NOTFOUND as the loop condition before the first fetch. Use the standard fetch-then-exit pattern.

22. What happens if you use %FOUND after a failed FETCH?

After a fetch that does not retrieve a row:

%FOUND = FALSE
%NOTFOUND = TRUE

23. Cursor Attributes with Cursor FOR LOOP

In a Cursor FOR LOOP, you generally do not need to manually check %FOUND or %NOTFOUND.

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

24. Explicit Cursor Attribute Example with %ROWTYPE

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp.employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp.first_name);
    DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || emp_cursor%ROWCOUNT);
END LOOP;

CLOSE emp_cursor;


END;
/

25. Frequently Asked Questions

Question	Answer
What are cursor attributes?	Properties that provide information about cursor processing.
What are the four main attributes?	%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN
Which attribute tells whether a row was found?	%FOUND
Which attribute tells whether no row was found?	%NOTFOUND
Which attribute gives the number of rows processed?	%ROWCOUNT
Which attribute tells whether a cursor is open?	%ISOPEN
What is the syntax for an implicit cursor attribute?	SQL%ROWCOUNT
What is the syntax for an explicit cursor attribute?	emp_cursor%ROWCOUNT
Which attribute is commonly used to exit an explicit cursor loop?	%NOTFOUND
What does %ROWCOUNT mean for an explicit cursor?	The number of rows fetched so far.
What does SQL%ROWCOUNT mean for an implicit cursor?	The number of rows affected by the SQL statement.
What is SQL%ISOPEN after an implicit SQL statement completes?	FALSE.

Important Exam Points

1. Cursor attributes provide information about cursor processing.
2. The four main attributes are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
3. Implicit cursor syntax uses SQL%attribute.
4. Explicit cursor syntax uses cursor_name%attribute.
5. %FOUND means row found or affected.
6. %NOTFOUND means no row found or affected.
7. %ROWCOUNT means number of rows processed.
8. For explicit cursors, %ROWCOUNT increases after successful fetches.
9. %ISOPEN indicates cursor open status.
10. SQL%ISOPEN is FALSE after the implicit statement has completed.
11. %NOTFOUND is commonly used to exit explicit cursor loops.
12. Cursor attributes relate to the most recent relevant cursor operation.

Quick Revision Table

Attribute	Explicit Cursor	Implicit Cursor
Found	emp_cursor%FOUND	SQL%FOUND
Not found	emp_cursor%NOTFOUND	SQL%NOTFOUND
Row count	emp_cursor%ROWCOUNT	SQL%ROWCOUNT
Is open	emp_cursor%ISOPEN	SQL%ISOPEN

One-line memory trick: FOUND = Did it find? NOTFOUND = Did it fail to find? ROWCOUNT = How many? ISOPEN = Is the cursor open?

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.

Oracle Parameterized Cursor Faqs With Examples

1. What is a Parameterized Cursor?

A parameterized cursor is a cursor declaration that contains one or more parameters.

Syntax:

CURSOR cursor_name (parameter_name datatype) IS
    SELECT_statement;

Example:

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    NULL;
END;
/

Here, p_dept_id is the cursor parameter.

2. Why do we use Parameterized Cursors?

The main purpose is reusability. Instead of creating separate cursors for each department, you can define one cursor and use it with different values.

OPEN emp_cursor(10);
OPEN emp_cursor(20);
OPEN emp_cursor(30);

3. Basic Syntax

CURSOR cursor_name (
    parameter1 datatype,
    parameter2 datatype
) IS
    SELECT ...
    FROM ...
    WHERE ...;

Example:

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary;
BEGIN
    NULL;
END;
/

4. How do we OPEN a Parameterized Cursor?

Pass the parameter values when opening the cursor.

OPEN emp_cursor(10, 50000);

Here, p_dept_id = 10 and p_min_salary = 50000.

5. Complete Example with OPEN, FETCH and CLOSE

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;


v_id     employees.employee_id%TYPE;
v_name   employees.first_name%TYPE;
v_salary employees.salary%TYPE;


BEGIN
OPEN emp_cursor(10);


LOOP
    FETCH emp_cursor INTO v_id, v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;

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

CLOSE emp_cursor;


END;
/

Flow:

DECLARE cursor
      ↓
OPEN cursor with parameter
      ↓
FETCH rows
      ↓
PROCESS rows
      ↓
%NOTFOUND
      ↓
CLOSE cursor

6. Can we use the same cursor with different values?

Yes. This is one of the biggest advantages.

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    OPEN emp_cursor(10);
    CLOSE emp_cursor;


OPEN emp_cursor(20);
CLOSE emp_cursor;

OPEN emp_cursor(30);
CLOSE emp_cursor;


END;
/

7. Can a Parameterized Cursor have multiple parameters?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary;
BEGIN
    OPEN emp_cursor(10, 50000);
    CLOSE emp_cursor;
END;
/

8. Can parameters have default values?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_dept_id NUMBER DEFAULT 10
    ) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    OPEN emp_cursor;
    CLOSE emp_cursor;


OPEN emp_cursor(20);
CLOSE emp_cursor;


END;
/

9. Can we use a VARCHAR2 parameter?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_name VARCHAR2
    ) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE first_name = p_name;
BEGIN
    OPEN emp_cursor('Ravi');
    CLOSE emp_cursor;
END;
/

10. Can we use DATE parameters?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_hire_date DATE
    ) IS
        SELECT employee_id, first_name, hire_date
        FROM employees
        WHERE hire_date >= p_hire_date;
BEGIN
    OPEN emp_cursor(DATE '2025-01-01');
    CLOSE emp_cursor;
END;
/

11. Can we use parameters in the WHERE clause?

Yes. This is the most common use.

CURSOR emp_cursor(p_dept_id NUMBER) IS
    SELECT employee_id, first_name
    FROM employees
    WHERE department_id = p_dept_id;

12. Can parameters be used in other parts of the query?

Yes.

DECLARE
    CURSOR emp_cursor (
        p_min_salary NUMBER
    ) IS
        SELECT employee_id,
               first_name,
               salary,
               salary * 12 AS annual_salary
        FROM employees
        WHERE salary >= p_min_salary
        ORDER BY salary DESC;
BEGIN
    OPEN emp_cursor(50000);
    CLOSE emp_cursor;
END;
/

13. Can we use a Parameterized Cursor with a Cursor FOR LOOP?

Yes. This is a very common and convenient approach.

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 || ' - ' ||
            emp.salary
        );
    END LOOP;
END;
/

No explicit OPEN, FETCH, or CLOSE is required.

14. Can we use different parameter values in different FOR LOOPS?

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('Dept 10: ' || emp.first_name);
    END LOOP;


FOR emp IN emp_cursor(20)
LOOP
    DBMS_OUTPUT.PUT_LINE('Dept 20: ' || emp.first_name);
END LOOP;


END;
/

15. Normal Cursor vs Parameterized Cursor

Normal Cursor	Parameterized Cursor
Fixed query values	Values can be supplied
Less reusable	More reusable
No cursor parameters	Has cursor parameters
OPEN emp_cursor	OPEN emp_cursor(10)

16. Are cursor parameters variables?

They behave as input values to the cursor query.

17. Are cursor parameters IN, OUT, or IN OUT?

Cursor parameters are input parameters.

18. Can we change a cursor parameter after opening the cursor?

No. If you need a different value, close the cursor and open it again.

19. Can we use variables as parameter values?

Yes.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;


v_dept_id NUMBER := 10;


BEGIN
OPEN emp_cursor(v_dept_id);
CLOSE emp_cursor;
END;
/

20. Can we use expressions as parameter values?

Yes.

OPEN emp_cursor(5 + 5);

21. Can we use a parameter in a Cursor FOR LOOP?

Yes.

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

22. Can we use FOR UPDATE with a Parameterized Cursor?

Yes.

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


COMMIT;


END;
/

23. Can we use WHERE CURRENT OF?

Yes, when the cursor is appropriately declared with FOR UPDATE.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, salary
        FROM employees
        WHERE department_id = p_dept_id
        FOR UPDATE;
BEGIN
    FOR emp IN emp_cursor(10)
    LOOP
        UPDATE employees
        SET salary = salary + 1000
        WHERE CURRENT OF emp_cursor;
    END LOOP;


COMMIT;


END;
/

24. Can a Parameterized Cursor return zero rows?

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(9999)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;


DBMS_OUTPUT.PUT_LINE('Processing complete');


END;
/

25. Can a Parameterized Cursor return multiple rows?

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;
END;
/

26. Can we use a parameterized cursor inside another loop?

Yes. This is useful for parent-child processing.

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;
/

27. Complete Example with Multiple Parameters

DECLARE
    CURSOR emp_cursor(
        p_dept_id NUMBER,
        p_min_salary NUMBER
    ) IS
        SELECT employee_id,
               first_name,
               salary
        FROM employees
        WHERE department_id = p_dept_id
          AND salary >= p_min_salary
        ORDER BY salary DESC;
BEGIN
    FOR emp IN emp_cursor(10, 50000)
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp.employee_id ||
            ', Name: ' || emp.first_name ||
            ', Salary: ' || emp.salary
        );
    END LOOP;
END;
/

28. Parameterized Cursor with Default Parameter

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

29. Parameterized Cursor with Record

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor(10);


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_emp.employee_id || ' - ' ||
        v_emp.first_name || ' - ' ||
        v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

30. What is the scope of a cursor parameter?

The parameter can be referenced within the cursor's query.

31. What happens if we don't supply a required parameter?

If a parameter has no default value, you must supply it when opening the cursor or using it in a Cursor FOR LOOP.

32. What is the main advantage of Parameterized Cursors?

Reusability. One cursor can be used for different conditions.

33. What is the difference between a Parameterized Cursor and a Dynamic SQL statement?

A parameterized cursor changes the values used by a predefined query. Dynamic SQL is used when the SQL statement itself needs to be constructed or changed at runtime.

34. What are the advantages of Parameterized Cursors?

1. Reusable cursor definition
2. Accept different input values
3. Avoid duplicate cursor declarations
4. Useful for filtering data dynamically
5. Easy to combine with Cursor FOR LOOP
6. Can have multiple parameters
7. Parameters can have default values
8. Can be used with FOR UPDATE

35. What are the disadvantages?

Parameterized cursors are still row-oriented cursor processing. If the entire operation can be performed with a single SQL statement, a set-based approach may be simpler and more efficient.

36. Parameterized Cursor vs Normal Explicit Cursor

Feature	Normal Cursor	Parameterized Cursor
Parameters	No	Yes
Reusable with different values	Limited	Yes
Example	OPEN emp_cursor	OPEN emp_cursor(10)
Query	Fixed values/conditions	Values supplied at runtime

37. Parameterized Cursor vs Cursor FOR LOOP

They are not alternatives in the same sense. A parameterized cursor defines the reusable query, while a Cursor FOR LOOP controls how the rows are processed. They can be used together.

38. Frequently Asked Questions

Question	Answer
What is a Parameterized Cursor?	A cursor that accepts input parameters when it is opened or invoked.
Why use it?	For reusability with different values.
How do you declare one?	CURSOR emp_cursor(p_dept_id NUMBER) IS SELECT ...
How do you open one?	OPEN emp_cursor(10);
Can it have multiple parameters?	Yes.
Can parameters have default values?	Yes.
Can we use variables as parameter values?	Yes.
Can we use a Parameterized Cursor with a Cursor FOR LOOP?	Yes.
Can we use FOR UPDATE?	Yes.
Can we use WHERE CURRENT OF?	Yes, with an appropriate FOR UPDATE cursor.

Important Exam Points

1. Parameterized Cursor = Explicit cursor with parameters.
2. Parameters are supplied when the cursor is opened or invoked.
3. Basic declaration: CURSOR c(p_value NUMBER) IS SELECT ...;
4. Open syntax: OPEN c(value);
5. Parameters are input values to the cursor query.
6. A cursor can have multiple parameters.
7. Parameters can have default values.
8. Parameterized cursors are useful for reusability.
9. They work with Cursor FOR LOOP.
10. They can be used with FOR UPDATE.
11. WHERE CURRENT OF can be used with an appropriate FOR UPDATE cursor.
12. Different cursor instances can use different parameter values.
13. A required parameter must be supplied unless a default value is defined.
14. If you need a different value for an already-open cursor, close it and open it again.
15. Use set-based SQL instead of row-by-row cursor processing when the task can be expressed cleanly as one SQL statement.

Easy Memory Trick

PARAMETERIZED CURSOR
        ↓
     One Cursor
        ↓
  +-----+-----+
  |     |     |
  10    20    30
  |     |     |
Dept10 Dept20 Dept30

Parameterized Cursor = One reusable cursor + different input values.