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.