What is the difference between the implicit and explicit cursors?

by

Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.

Explicit cursor is created for queries returning multiple rows.

In PL/SQL, implicit and explicit cursors refer to two different ways of handling queries and processing result sets.

  1. Implicit Cursors:
    • Implicit cursors are automatically created by Oracle whenever a SQL statement is executed in PL/SQL.
    • They are used for SQL statements like SELECT INTO, FETCH INTO, and RETURNING INTO, where the result set is expected to contain a single row.
    • The processing of implicit cursors is automatic, and developers don’t have to explicitly declare or manage them.

Example of implicit cursor with SELECT INTO:

DECLARE
v_employee_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
  1. Explicit Cursors:
    • Explicit cursors are declared and managed by the developer using the DECLARE, OPEN, FETCH, and CLOSE statements.
    • They provide more control over result set processing, especially when dealing with multiple rows or when using dynamic SQL.
    • Developers need to explicitly declare the cursor, open it, fetch the data, and close it.

Example of explicit cursor:

DECLARE
CURSOR emp_cursor IS
SELECT employee_id, employee_name FROM employees WHERE department_id = 10;
v_emp_id employees.employee_id%TYPE;
v_emp_name employees.employee_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_id, v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Employee Name: ' || v_emp_name);
END LOOP;
CLOSE emp_cursor;
END;

In summary, implicit cursors are automatically created by the system for certain types of SQL statements, while explicit cursors are declared and controlled by the developer, providing more flexibility and control over result set processing.