How do cursors work in Oracle — explicit and implicit, and how do they differ?

In Oracle, cursors are used to manage query results and allow row-by-row processing of data.

Implicit cursors are automatically created when executing SQL queries (e.g., SELECT INTO, DML operations). They provide information about the execution of the query (such as the number of processed rows), but do not allow direct access to row-by-row processing of the result.

Explicit cursors are declared by the programmer in PL/SQL and allow more flexible data retrieval: opening the cursor, fetching rows one by one or in batches, and closing the cursor. This is useful when you need to process many rows in a loop.

Differences:

• Implicit cursors are automatic, explicit ones are declared manually.
• Explicit cursors allow row-by-row processing and control, implicit cursors are only for simple operations.

Example of an explicit cursor:

DECLARE
  CURSOR c_emp IS SELECT employee_id, salary FROM employees WHERE department_id = 10;
  v_emp_id employees.employee_id%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp_id, v_salary;
    EXIT WHEN c_emp%NOTFOUND;
    -- data processing
    DBMS_OUTPUT.PUT_LINE('Employee ' || v_emp_id || ': ' || v_salary);
  END LOOP;
  CLOSE c_emp;
END;