Explicit Cursors in PL/SQL

In PL/SQL cursors are a fundamental concept used to handle SQL queries that return multiple rows. There are two main types of cursors in PL/SQL, implicit and explicit cursors. While i

mplicit cursors are automatically managed by Oracle for simple SQL statements, explicit cursors give developers full control over the process of querying multiple rows and fetching data one at a time. This control is essential when dealing with complex queries or operations that require row-by-row processing. This article explores Working with Cursors in PL/SQL, Explicit Cursor Syntax in PL/SQL, usage, examples, and best practices. By the end, you’ll have a thorough understanding of how to work with cursors in PL/SQL.

What are Explicit Cursors in PL/SQL?

An explicit cursor is a user-defined cursor for queries that return multiple rows. While in the case of an implicit cursor, PL/SQL handles the query and the result set internally, in the case of an explicit cursor, you have to process query results one by one. That level of control would be particularly useful for fetching and processing data one row at a time, or for dealing with a large amount of data.

Explicit cursors in PL/SQL allow you to:

• Define the cursor explicitly.
• Open the cursor to execute the query.
• Fetch rows from the result set.
• Process the rows as needed.
• Close the cursor once processing is complete.

Why Use Explicit Cursors in PL/SQL?

• Greater Control: Unlike implicit cursors, explicit cursors give you more control over the lifecycle of the cursor, allowing you to fetch rows incrementally and process them.
• Efficient Data Handling: Explicit cursors are well-suited for handling queries that return large datasets or when you need row-by-row processing.
• Structured Code: They make it easier to structure PL/SQL programs, allowing you to separate the query logic from the data-processing logic.

Differences Between Implicit and Explicit Cursors

The table below highlights the key differences between implicit and explicit cursors:

Feature	Implicit Cursor	Explicit Cursor
Cursor Declaration	Handled automatically by PL/SQL	Manually declared by the developer
Management	Automatically managed	Requires manual management (open, fetch, close)
Control	Limited control over execution	Full control over execution and data fetching
Complexity	Simpler to use for basic operations	More complex, but provides flexibility for multi-row processing
Usage	Best for simple DML operations	Ideal for handling multi-row queries

Syntax of Explicit Cursors in PL/SQL

To use explicit cursors in PL/SQL, you need to follow a specific syntax to declare, open, fetch, and close the cursor.

Explicit Cursor Syntax in PL/SQL

• Declaring the Cursor: This step defines the cursor and associates it with a specific SQL query.

CURSOR cursor_name IS
  SELECT column1, column2, ... FROM table_name WHERE condition;

• Opening the Cursor: The OPEN statement executes the SQL query associated with the cursor and prepares the result set for fetching.

OPEN cursor_name;

• Fetching Rows: The FETCH statement retrieves the next row from the cursor’s result set into specified variables.

FETCH cursor_name INTO variable1, variable2, ...;

• Closing the Cursor: The CLOSE statement releases the resources associated with the cursor.

CLOSE cursor_name;

Example of Using an Explicit Cursor in PL/SQL

Let’s consider a practical example where we need to fetch and display employee details from a database table employees.

DECLARE
  -- Declare variables to hold fetched data
  v_employee_id employees.employee_id%TYPE;
  v_employee_name employees.employee_name%TYPE;
  
  -- Declare an explicit cursor
  CURSOR emp_cursor IS
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = 10;

BEGIN
  -- Open the cursor
  OPEN emp_cursor;
  
  -- Fetch rows one by one
  LOOP
    FETCH emp_cursor INTO v_employee_id, v_employee_name;
    
    -- Exit loop when no more rows are available
    EXIT WHEN emp_cursor%NOTFOUND;
    
    -- Process the fetched data (for example, display the employee details)
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);
  END LOOP;
  
  -- Close the cursor
  CLOSE emp_cursor;
END;

In this case,

• There is a declaration of the cursor that selects the details of the employee from an instance of the department.
• The cursor is opened and rows are fetched within a loop.
• The loop exits when all rows have been processed, and the cursor is then closed.

Cursor Attributes in PL/SQL

PL/SQL provides several cursor attributes that help you monitor the status of the cursor and the result set.

Cursor Attribute	Description
%FOUND	Returns TRUE if the last fetch found a row, otherwise FALSE.
%NOTFOUND	Returns TRUE if the last fetch did not find a row, otherwise FALSE.
%ROWCOUNT	Returns the number of rows fetched so far.
%ISOPEN	Returns TRUE if the cursor is open, otherwise FALSE.

Example of Cursor Attributes in PL/SQL

DECLARE
  v_employee_id employees.employee_id%TYPE;
  v_employee_name employees.employee_name%TYPE;
  
  CURSOR emp_cursor IS
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = 10;

BEGIN
  OPEN emp_cursor;
  
  LOOP
    FETCH emp_cursor INTO v_employee_id, v_employee_name;
    EXIT WHEN emp_cursor%NOTFOUND;  -- Use cursor attribute %NOTFOUND to exit loop
    
    -- Use cursor attributes to monitor cursor status
    DBMS_OUTPUT.PUT_LINE('Row fetched: ' || emp_cursor%ROWCOUNT);
  END LOOP;
  
  CLOSE emp_cursor;
END;

Advanced Cursor Features of PL/SQL

Explicit cursors of PL/SQL do much more than fetching rows. Let’s look into some advanced features.

Parameterised Cursors

A parameterized cursor can use parameters to accept input values to pass to a cursor, enabling it to be more flexible to build dynamic queries based on the actual value provided.

Parameterised Cursors Syntax

CURSOR cursor_name (parameter1 TYPE, parameter2 TYPE) IS
  SELECT column1, column2 FROM table_name WHERE condition;

Example of a Parameterized Cursor

DECLARE
  v_employee_id employees.employee_id%TYPE;
  v_employee_name employees.employee_name%TYPE;
  
  -- Declare a parameterized cursor
  CURSOR emp_cursor (p_department_id employees.department_id%TYPE) IS
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = p_department_id;

BEGIN
  -- Open the cursor with a parameter value
  OPEN emp_cursor(10);  -- Fetch employees from department 10
  
  LOOP
    FETCH emp_cursor INTO v_employee_id, v_employee_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);
  END LOOP;
  
  CLOSE emp_cursor;
END;

In this example, the cursor emp_cursor takes a department ID as a parameter, allowing it to fetch employee details dynamically based on the department.

Cursor FOR Loops

A cursor FOR loop simplifies the process of fetching rows from a cursor. The cursor is opened, fetched, and closed automatically within the loop.

Example of Cursor FOR Loop

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = 10;

BEGIN
  -- Using a FOR loop to iterate over the cursor
  FOR emp_rec IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.employee_name);
  END LOOP;
END;

Here, the FOR loop automatically handles opening, fetching, and closing the cursor. It reduces the need for manual cursor management, making the code more concise and readable.

Handling Exceptions with Cursors

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

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
    IF emp_cursor%ISOPEN THEN
      CLOSE emp_cursor;
    END IF;
END;

Advantages of Explicit Cursors in PL/SQL

Explicit cursors in PL/SQL give one more control and flexibility to work with the database. The developers define and control the cursor while working on hand, which is a more personalized approach toward retrieving and manipulating data. Keeping all that in mind, some of the main advantages are as follows:

1. More Control Over Data Retrieval

Explicit cursors allow the programmer to define a specified SQL statement and control explicitly the fetching of the data. This provides them with the kind of functionality which can process complex queries and multiple rows much better than that of implicit cursors.

2. Capability of Handling Multi-Row Queries

Unlike implicit cursors, explicit cursors can retrieve data from more than one row. This is very helpful when one needs to process large result sets larger than one row in queries; it’s a great way to process bulk data efficiently.

3. Better Flexibility in Fetching Data

You can fetch a rowset with explicit cursors in a way that suits your application logic. For instance, you can fetch rows one at a time or in groups through methods such as FETCH accompanying a condition that controls the fashion in which the rowset is fetched.

4. Cursor Attributes to Monitor

Explicit cursors provide access to many attributes, for example: %ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN allowing developers to track the cursor state and the operation results. This is very precious in debugging and in conditional logic depending on the results.

5. Robust Exception Handling

Explicit cursors enable exact error handling on SQL statements. Specific exception handling is possible for developers to deal with different scenarios, such as NO_DATA_FOUND or TOO_MANY_ROWS. This makes the code more robust as regards the possible issues that might be raised.

6. Reusability of Cursors

Once declared, explicit cursors may be opened, fetched from, and closed multiple times within the same block or procedure. This reusability will result in performance gains only when the data is accessed iteratively in a single environment.

7. Better Performance for Complex Operations

Explicit cursors may also be optimized for execution performance when queries are particularly complex or very large result sets have to be returned, or in case a number of processing steps have to be done. When using explicit cursors, developers always have the option of fine-tuning queries and fetching strategies to get maximum efficiency.

8. Easier Code

Using explicit cursors often means having structured code. The application of explicit cursors is obviously followed through at the time of data retrieval as well as processing, therefore the readability and maintainability of the code improve.

9. Capability to handle more than one result set

For their part, explicit cursors can be used to manage the presence of several result sets in a single block of PL/SQL, thus giving easier control and management of data even when dealing with various datasets to be processed concurrently.

10. Better facility for debugging

Management of explicit cursors has better debugging facilities. Developers can insert statements of debugging that can trace the state of cursors as well as the data.

Disadvantages of Explicit Cursors in PL/SQL

Notwithstanding this, explicit cursors in PL/SQL have also some disadvantages and disadvantage. Such disadvantages can affect the complexity of the code, performance, and resource management. Here are the main disadvantages of explicit cursors in PL/SQL :

1. Increased Complexity

Explicit cursors, compared to implicit cursors, require more code and management, and hence may be more complex. Developers must actually declare a cursor, open it, fetch records, and close it, which of course results in somewhat more verbose and difficult-to-read code.

2. Manual Resource Management

Using explicit cursors, a developer needs to handle the cursor lifecycle, that is open and close the cursor. If they forget to close a cursor resource leaks may occur, meaning it may degrade application performance and even exhaust resources in a database.

3. Performance Overhead

Some very simple queries require some overhead of defining and managing explicit cursors, which is likely to degrade performance, since an implicit cursor would be more than enough in such cases. Opening, fetching, and closing may be required with unnecessary overhead in execution time and complexity.

4. Risk of Errors in Cursor Management

The explicit cursor handling process is done by hand; therefore, there is the increased chance of making mistakes while coding those cases. Developers may forget closing a cursor. They can perform exception handling incorrectly and get bizarre behavior along with resource leakage.

5. Not so Ideal for Simple Queries

For simple, single-row queries, explicit cursors are perhaps overkill. In these simple cases, implicit cursors leave for cleaner as well as efficient code while adding unwanted complexity by explicit cursors.

6. Greater Learning Curve

The most cumbersome part of explicit cursors is the fact that they are a bit more complex to understand and create compared to implicit cursors, especially for the new developers in PL/SQL, which will extend time needed for training and development, especially if they don’t know cursor management concepts.

7. Duplicate Code

If many explicit cursors are needed for similar work, this causes code duplication. The developer has to copy cursor definitions and fetch logic resulting in a higher maintenance burden and making the codebase harder to maintain.

8. Inability to Debug

Even though cursors can have a kind of debug as cursors explicit, it is cumbersome to debug when a large number of cursors is kept or complex operations involving cursors. It will be very confusing while debugging when know which one cursor is the culprit or which are some cursors in unknown state.

9. Possibility of Not Executing the Query as Efficiently

Explicit cursors may make the database optimizer exclude generation of a most optimal execution plan. Increasing complexity in cursor logic typically means far less than optimal execution time for the query. This is especially so when compared to using certain other even simpler queries with implicit cursors.

10. Not Ideal for Bulk Processing

Explicit cursors are probably not the best choice for dealing with a lot of data. There are mechanisms available in PL/SQL, for example, in the form of bulk processing statements, such as FLORAL or BULK COLLECT, which would typically be a better choice for these scenarios, possibly making explicit cursors more code-complexity intensive.