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.

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)