Cursor in PLSQL

Cursor Basics

In PL/SQL, explicit cursors can be used in two primary ways: by manually controlling the cursor using OPEN, FETCH, and CLOSE statements, or by using a cursor FOR loop which automates these steps.

1. Using FETCH with Manual Control of the Cursor

In this method, you must manually:

• Open the cursor.
• Fetch rows using FETCH.
• Close the cursor.

2. Using a Cursor FOR Loop

With a cursor FOR loop, PL/SQL automatically:

• Opens the cursor.
• Fetches each row into a record.
• Closes the cursor when all rows are processed.

Step 1: Create a Sample Table

We'll start by creating a sample table called employees and inserting some data into it.

Table Creation

-- Create a sample table 'employees'
CREATE TABLE employees (
    employee_id   NUMBER(5),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50)
);

Insertion into Table

-- Insert sample data into the 'employees' table
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (1, 'John', 'Doe');
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (2, 'Jane', 'Smith');
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (3, 'Alice', 'Johnson');
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (4, 'Bob', 'Brown');
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (5, 'Charlie', 'Davis');

Commit the transaction to save the data

COMMIT;

Step 2: Using Manual Cursor with FETCH

Now, let’s demonstrate how to use an explicit cursor with FETCH to retrieve and process the data row by row.

DECLARE
   -- Declare the cursor
   CURSOR emp_cursor IS
      SELECT employee_id, first_name, last_name FROM employees;

   -- Declare variables to store the fetched data
   v_emp_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;

BEGIN
   -- Open the cursor
   OPEN emp_cursor;

   -- Fetch each row from the cursor
   LOOP
      FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name;

      -- Exit the loop when no more rows are fetched
      EXIT WHEN emp_cursor%NOTFOUND;

      -- Display the data
      DBMS_OUTPUT.PUT_LINE
('ID: ' || v_emp_id || ', Name: ' || v_first_name || ' ' || v_last_name);
   END LOOP;

   -- Close the cursor
   CLOSE emp_cursor;
END;

Expected Output:

ID: 1, Name: John Doe
ID: 2, Name: Jane Smith
ID: 3, Name: Alice Johnson
ID: 4, Name: Bob Brown
ID: 5, Name: Charlie Davis

Explanation:

• 1. The cursor emp_cursor selects the employee_id, first_name, and last_name from the employees table.
• 2. The OPEN statement opens the cursor.
• 3. The FETCH statement retrieves each row from the cursor and stores it in the variables v_emp_id, v_first_name, and v_last_name.
• 4. The loop continues until there are no more rows to fetch (emp_cursor%NOTFOUND).
• 5. The cursor is closed using CLOSE after all rows are processed.

Step 3: Using a Cursor FOR Loop

Here’s how you can achieve the same result with a cursor FOR loop. This method eliminates the need for OPEN, FETCH, and CLOSE statements.

DECLARE
   -- Declare the cursor
   CURSOR emp_cursor IS
      SELECT employee_id, first_name, last_name FROM employees;
BEGIN
   -- Use a Cursor FOR Loop to process each row
   FOR emp_record IN emp_cursor LOOP
      DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.employee_id || ', Name: ' 
|| emp_record.first_name || ' ' || emp_record.last_name);
   END LOOP;
END;

Expected Output:

ID: 1, Name: John Doe
ID: 2, Name: Jane Smith
ID: 3, Name: Alice Johnson
ID: 4, Name: Bob Brown
ID: 5, Name: Charlie Davis

Explanation:

• 1. The cursor emp_cursor is declared the same way as before.
• 2. The cursor FOR loop automatically handles the cursor's lifecycle, opening it at the start of the loop, fetching each row, and closing it when the loop finishes.
• 3. Each row is fetched into the emp_record variable, and its fields can be accessed using dot notation (emp_record.employee_id, emp_record.first_name, etc.).
• 4. No need to explicitly OPEN, FETCH, or CLOSE the cursor—PL/SQL takes care of this for you.

Conclusion:

Manual cursor with FETCH is useful when you need fine-grained control over the cursor's behavior, such as conditionally fetching rows or performing operations between fetches.

• Use this method if you need finer control over cursor operations,
• such as handling partial results, conditional fetching,
• or performing complex logic between fetches.

Cursor FOR loop is more convenient for iterating through all rows
in a cursor without needing to manually manage the cursor lifecycle.
It simplifies the code and is generally preferred when processing
all rows sequentially.

• Use this method for simplicity when you need to fetch and process
• all rows from the cursor without additional control requirements.
• It automatically manages the cursor lifecycle and
• reduces boilerplate code.

Cursor Attributes: -

%FOUND: Returns TRUE if the last fetch returned a row, otherwise FALSE.
%NOTFOUND: Returns TRUE if the last fetch did not return 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, cursors are used to retrieve rows from a database table. Cursor attributes provide information about the execution of a cursor. Below is a block of PL/SQL code that demonstrates the different types of cursor attributes, along with explanations for each.

DECLARE
    -- Declare a cursor for fetching employee details
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees;

    -- Variables to hold fetched values
    v_employee_id employees.employee_id%TYPE;
    v_first_name  employees.first_name%TYPE;
    v_last_name   employees.last_name%TYPE;

BEGIN
    -- Open the cursor
    OPEN emp_cursor;

    -- Fetch rows from the cursor
    LOOP
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;

        -- Exit when no more rows to fetch
        EXIT WHEN emp_cursor%NOTFOUND;

        -- Display fetched data
        DBMS_OUTPUT.PUT_LINE
('ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' 
|| v_last_name);
    END LOOP;

    -- Display the number of rows fetched
    DBMS_OUTPUT.PUT_LINE('Total Rows Fetched: ' || emp_cursor%ROWCOUNT);

    -- Close the cursor
    CLOSE emp_cursor;

    -- Display if cursor is closed
    IF emp_cursor%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('Cursor is still open.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Cursor is closed.');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        -- Handle any exceptions
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        -- Ensure the cursor is closed if there is an error
        IF emp_cursor%ISOPEN THEN
            CLOSE emp_cursor;
        END IF;
END;

Explanation of Cursor Attributes

%FOUND:

Usage: emp_cursor%FOUND
Description: This attribute returns TRUE if the last fetch operation retrieved a row. It is typically used to check if the fetch was successful.

%NOTFOUND:

Usage: emp_cursor%NOTFOUND
Description: This attribute returns TRUE if the last fetch operation did not retrieve any rows. In the provided code, it is used to exit the loop when there are no more rows to fetch.

%ROWCOUNT:

Usage: emp_cursor%ROWCOUNT
Description: This attribute returns the number of rows that have been fetched so far. In the example, it shows how many rows were fetched after the loop completes.

%ISOPEN:

Usage: emp_cursor%ISOPEN
Description: This attribute returns TRUE if the cursor is currently open. The example checks if the cursor is still open after closing it and provides a message accordingly.

Execution Flow

• The cursor emp_cursor is declared to select employee details from the employees table.
• The cursor is opened, and a loop is used to fetch each row one by one.

Inside the loop:

• Each fetched row is displayed.
• The loop exits when there are no more rows to fetch (%NOTFOUND).
• After exiting the loop, the total number of fetched rows is displayed using %ROWCOUNT.
• The cursor is then closed, and its status is checked using %ISOPEN.
• Any exceptions that occur are caught and handled, ensuring the cursor is closed if it was left open.
• This code provides a comprehensive example of using cursor attributes in PL/SQL.