Cursor and It's Types in Oracle Database

Implicit Cursor
Explicit Cursor
RefCursor
SysRefCursor

🔹 1. Implicit Cursor

Created automatically by Oracle when a DML statement (INSERT, UPDATE, DELETE) or a single-row SELECT INTO is executed.

You don’t declare or open it manually.

Useful for checking the status of the last DML using attributes like %ROWCOUNT, %FOUND, etc.

✅ Example:

BEGIN
UPDATE employees SET salary = salary + 1000 WHERE dept_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;

---

🔹 2. Explicit Cursor

Manually declared and controlled by the programmer.

Used to process multiple rows returned by a query.

Gives full control with OPEN, FETCH, CLOSE operations.

✅ Example:

DECLARE
CURSOR emp_cur IS SELECT emp_id, emp_name FROM employees;
v_id employees.emp_id%TYPE;
v_name employees.emp_name%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_id, v_name;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE emp_cur;
END;

---

🔹 3. RefCursor (Reference Cursor)

A cursor variable that can point to different SELECT queries at runtime.

Can be strongly or weakly typed.

Useful for returning query results from procedures/functions to other programs or layers.

✅ Example:

TYPE emp_refcur IS REF CURSOR;
emp_cursor emp_refcur;

OPEN emp_cursor FOR SELECT * FROM employees;

---

🔹 4. SYS_REFCURSOR

A predefined weak RefCursor provided by Oracle.

You don’t need to declare a type — it's ready to use.

Ideal for flexible, dynamic result sets, especially in stored procedures and APIs.

✅ Example:

PROCEDURE get_emps(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR SELECT emp_id, emp_name FROM employees;
END;

---

✅ Summary Table

Cursor Type Declared By Static/Dynamic Used For

Implicit Oracle Static Auto-handled DML / single SELECT
Explicit Developer Static Row-by-row logic with fixed queries
RefCursor Developer Dynamic Pass dynamic query results
SYS_REFCURSOR Oracle Dynamic Same as RefCursor, but predefined