1. What are cursor attributes in Oracle?
Cursor attributes are built-in
properties that provide information about the execution status of a cursor.
They help determine:
- Whether rows were returned
- How many rows were processed
- Whether the cursor is open
They can be used with:
- Implicit cursors (SQL% prefix)
- Explicit cursors (cursor_name% prefix)
2. What are the main cursor attributes?
|
Attribute |
Description |
Applies To |
|
%FOUND |
TRUE if the last fetch or DML affected/returned ≥1 row |
Implicit & Explicit |
|
%NOTFOUND |
TRUE if the last fetch or DML affected/returned 0 rows |
Implicit & Explicit |
|
%ROWCOUNT |
Number of rows fetched or affected so far |
Implicit & Explicit |
|
%ISOPEN |
TRUE if cursor is open, FALSE if closed |
Explicit only (Implicit cursors auto-close) |
3. How is %FOUND used?
- Returns TRUE if a row was fetched (explicit) or affected (implicit).
Implicit cursor example:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Rows updated.');
END IF;
Explicit cursor example:
DECLARE
CURSOR emp_cursor IS
SELECT * FROM employees WHERE department_id = 10;
v_id employees.employee_id%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_id;
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Row fetched: ' || v_id);
END IF;
CLOSE emp_cursor;
END;
4. How is %NOTFOUND used?
- Returns TRUE if no rows were returned/fetched.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id FROM employees WHERE department_id = 999;
v_id employees.employee_id%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_id;
IF emp_cursor%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows found.');
END IF;
CLOSE emp_cursor;
END;
5. How is %ROWCOUNT used?
- Returns number of rows fetched or affected.
Implicit cursor example:
DELETE FROM employees WHERE department_id = 50;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.');
Explicit cursor example:
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id;
EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT || ' rows fetched.');
6. How is %ISOPEN used?
- Only for explicit cursors
- Returns TRUE if cursor is open, FALSE if closed
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open.');
END IF;
CLOSE emp_cursor;
IF NOT emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is closed.');
END IF;
7. Can cursor attributes be used with loops?
Yes. They are often used in row-by-row processing with explicit cursors.
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total rows fetched: ' || emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
8. Difference between implicit and explicit cursor attributes
|
Attribute |
Implicit Cursor |
Explicit Cursor |
|
%FOUND |
SQL%FOUND |
cursor_name%FOUND |
|
%NOTFOUND |
SQL%NOTFOUND |
cursor_name%NOTFOUND |
|
%ROWCOUNT |
SQL%ROWCOUNT |
cursor_name%ROWCOUNT |
|
%ISOPEN |
Not applicable |
cursor_name%ISOPEN |
9. Common mistakes with cursor attributes
- Using %FOUND before the first fetch (explicit cursor) → always FALSE
- Forgetting to close explicit cursor but checking %ISOPEN later
- Mixing implicit (SQL%) and explicit (cursor_name%) attributes
- Assuming %ROWCOUNT works like COUNT(*) – it counts processed rows, not total rows in table
10. Best practices
- Use Cursor FOR Loops when possible – reduces need for %ISOPEN and manual fetching
- Always check %NOTFOUND to avoid infinite loops
- Use %ROWCOUNT to validate DML operations
- Use explicit cursor attributes when precise control over row-by-row processing is needed
No comments:
Post a Comment