Difference Between Implicit and Explicit Cursor in Oracle PLSQL

In this Oracle Blog Post, We are sharing most asked interview question in Oracle PLSQL which is difference between  Implicit and Explicit Cursor  in Oracle PLSQL.

Implicit vs Explicit Cursor in Oracle PLSQL: Understanding the Difference

When working with Oracle databases and its procedural language extension, PL/SQL, you'll inevitably need to interact with data retrieved from SQL queries. Cursors are fundamental mechanisms that allow PL/SQL to handle the result sets of these queries, essentially acting as pointers to the memory area where the query results are processed.

Oracle provides two main types of cursors: Implicit Cursors and Explicit Cursors. Understanding when and how to use each is crucial for writing efficient and effective PL/SQL code. Let's dive into the details.

What is Cursor, Briefly?

At its core, a cursor is a pointer to a private SQL area, a region in memory allocated by Oracle to process SQL statements. This area contains information about the statement, including the parsed representation and, for queries, the result set (or a pointer to it). Cursors allow your PL/SQL code to process rows returned by a query, potentially one at a time.

Implicit Cursors: The Automatic Choice

Oracle automatically manages implicit cursors whenever you execute a SQL DML statement (like INSERT, UPDATE, DELETE) or a SELECT INTO statement that is expected to return only one row. You don't declare them; Oracle handles the opening, fetching, and closing behind the scenes.

Key Characteristics:

1. Automatic: Managed entirely by Oracle.
2. No Declaration: You don't write code to declare an implicit cursor.
3. Default Name: Oracle uses the default identifier SQL to refer to the most recently executed implicit cursor.
4. Use Cases: Primarily for DML statements and single-row SELECT INTO queries.

Cursor Attributes:

Even though they are managed implicitly, you can access information about the execution of the most recent SQL statement using cursor attributes prefixed with SQL%:

• SQL%FOUND: Boolean. TRUE if the recent DML statement affected one or more rows, or if a SELECT INTO returned a row. FALSE otherwise.
• SQL%NOTFOUND: Boolean. The logical opposite of SQL%FOUND. TRUE if the recent DML affected zero rows, or if SELECT INTO returned no rows.
• SQL%ROWCOUNT: Number. Returns the number of rows affected by the most recent DML statement. For SELECT INTO, it's 1 if a row is found, 0 otherwise (though NO_DATA_FOUND exception is usually raised before you check if no row is found).
• SQL%ISOPEN: Boolean. Always FALSE for implicit cursors because Oracle opens and closes them automatically around the statement execution.

Example 1: Using Implicit Cursor with UPDATE

Let's assume we have an EMPLOYEE table.

SQL:-

DECLARE
  v_dept_id employees.department_id%TYPE := 90; -- Example department ID
BEGIN
  -- Update the salary for employees in a specific department
  UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = v_dept_id;

  -- Check if any rows were updated
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Update successful. ' || SQL%ROWCOUNT || ' employee(s) updated in department ' || v_dept_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employees found in department ' || v_dept_id || '. No updates made.');
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Example 2: Using Implicit Cursor with SELECT INTO

This statement implicitly uses a cursor. Oracle expects exactly one row.

SQL:-

DECLARE
  v_employee_name employees.first_name%TYPE;
  v_salary        employees.salary%TYPE;
  v_employee_id   employees.employee_id%TYPE := 100; -- Example employee ID
BEGIN
  -- Retrieve details for a specific employee
  SELECT first_name, salary
  INTO v_employee_name, v_salary
  FROM employees
  WHERE employee_id = v_employee_id;

  -- If SELECT INTO succeeds, SQL%FOUND is TRUE and SQL%ROWCOUNT is 1
  -- We typically don't check SQL%FOUND here because exceptions handle the failure cases.
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ', Salary: ' || v_salary);

EXCEPTION
  WHEN NO_DATA_FOUND THEN -- Specific exception if no rows are returned
    DBMS_OUTPUT.PUT_LINE('Employee with ID ' || v_employee_id || ' not found.');
  WHEN TOO_MANY_ROWS THEN -- Specific exception if more than one row is returned
    DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found with ID ' || v_employee_id || '.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Watch Out: The primary challenge with SELECT INTO (and implicit cursors) is that it must return exactly one row. If it returns zero rows, Oracle raises NO_DATA_FOUND. If it returns more than one row, Oracle raises TOO_MANY_ROWS. You need to handle these exceptions explicitly.

Explicit Cursors: Taking Control

When you need to process a query that might return multiple rows (or zero rows, without raising an error immediately), you need an explicit cursor. You, the developer, declare, open, fetch rows from, and close the cursor manually.

Key Characteristics:

1. Manual Control: You explicitly manage the cursor's lifecycle.
2. Declaration: Declared in the DECLARE section of your PL/SQL block using the CURSOR cursor_name IS select_statement; syntax.
3. User-Defined Name: You give the cursor a meaningful name.
4. Use Cases: Processing multi-row result sets, row-by-row processing logic.

The Lifecycle:

1. DECLARE: Define the cursor and associate it with a SELECT statement.

CURSOR c_employee_cursor IS

  SELECT employee_id, first_name, salary

  FROM employees

  WHERE department_id = 90;

2. OPEN: Execute the query and populate the result set. The cursor now points before the first row.

OPEN c_employee_cursor;

3. FETCH: Retrieve the next row from the result set into variables. You typically do this inside a loop.

FETCH c_employee_cursor INTO v_emp_id, v_fname, v_salary;

4. CLOSE: Release the resources associated with the cursor. It's crucial to close cursors you open.

CLOSE c_employee_cursor;

Cursor Attributes:

Explicit cursors also have attributes, but you access them using the cursor's name:

• cursor_name%FOUND: Boolean. TRUE if the most recent Workspace returned a row. FALSE otherwise. Useful for loop termination.
• cursor_name%NOTFOUND: Boolean. The logical opposite of %FOUND. TRUE if the most recent Workspace did not return a row. Often used as the exit condition for a loop.
• cursor_name%ROWCOUNT: Number. The total number of rows fetched so far.
• cursor_name%ISOPEN: Boolean. TRUE if the cursor is open, FALSE otherwise.

Example: Using an Explicit Cursor

DECLARE
  -- Declare variables to hold fetched data
  v_emp_id    employees.employee_id%TYPE;
  v_fname     employees.first_name%TYPE;
  v_salary    employees.salary%TYPE;
  v_dept_id   employees.department_id%TYPE := 90; -- Department to query

  -- Declare the explicit cursor
  CURSOR c_employee_cursor IS
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE department_id = v_dept_id
    ORDER BY salary DESC;

BEGIN
  -- Open the cursor
  OPEN c_employee_cursor;

  DBMS_OUTPUT.PUT_LINE('Employees in Department ' || v_dept_id || ':');

  -- Loop to fetch rows
  LOOP
    -- Fetch the next row into variables
    FETCH c_employee_cursor INTO v_emp_id, v_fname, v_salary;

    -- Exit the loop if no more rows are found
    EXIT WHEN c_employee_cursor%NOTFOUND;

    -- Process the fetched row (e.g., print it)
    DBMS_OUTPUT.PUT_LINE(' ID: ' || v_emp_id || ', Name: ' || v_fname || ', Salary: ' || v_salary);

    -- Optional: Check row count during processing
    -- DBMS_OUTPUT.PUT_LINE(' Rows fetched so far: ' || c_employee_cursor%ROWCOUNT);

  END LOOP;

  -- Check how many rows were processed in total after the loop
  DBMS_OUTPUT.PUT_LINE('Total employees processed: ' || c_employee_cursor%ROWCOUNT);

  -- Close the cursor (VERY IMPORTANT!)
  CLOSE c_employee_cursor;

EXCEPTION
  WHEN OTHERS THEN
    -- Ensure cursor is closed even if an error occurs mid-processing
    IF c_employee_cursor%ISOPEN THEN
      CLOSE c_employee_cursor;
    END IF;
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Implicit vs. Explicit: Quick Comparison

Feature	Implicit Cursor (SQL%)	Explicit Cursor (cursor_name%)
Declaration	Automatic	Manual (CURSOR c IS SELECT...)
Naming	Default (SQL)	User-defined
Management	Oracle (Automatic Open/Fetch/Close)	Developer (Manual Open/Fetch/Close)
Row Handling	Single Row (SELECT INTO), DML	Multi-row (or single/zero)
Error Handling	Raises NO_DATA_FOUND/TOO_MANY_ROWS for SELECT INTO	Requires checking %FOUND/%NOTFOUND
Code Verbosity	Low	High
Typical Use	DML, Single-row lookups	Row-by-row processing of multi-row sets

Conclusion

• Use Implicit Cursors (via simple INSERT, UPDATE, DELETE, SELECT INTO) when dealing with DML or when you absolutely expect a query to return exactly one row. Be prepared to handle NO_DATA_FOUND and TOO_MANY_ROWS exceptions for SELECT INTO.
• Use Explicit Cursors when you need to process multiple rows from a query result set, requiring fine-grained control over fetching each row.