Question

Explain cursor with example.

Answer 1

Of course. Here is a detailed explanation of a cursor in SQL/PL/SQL, including its purpose, a step-by-step lifecycle, and a complete, practical example.

---

What is a Cursor?

A Cursor is a database object that acts as a pointer to a result set—the set of rows returned by a SQL query. It allows an application to process the rows of a result set one at a time, rather than processing the entire set at once.

Think of a cursor like a bookmark in a book. When you run a query, the database finds all the "pages" (rows) that match. The cursor is the bookmark you place on the first matching page, allowing you to read it, then move the bookmark to the next page, and so on, until you've processed all the relevant pages.

Why are Cursors Needed?

SQL is a set-based language, meaning its commands (like SELECT, UPDATE, DELETE) are designed to operate on entire sets of rows at once. However, procedural programming languages often need to perform complex logic or operations on each individual row. A cursor provides the mechanism to bridge this gap, allowing for this row-by-row processing.

The Cursor Lifecycle

Working with an explicit cursor in PL/SQL follows a clear, four-step lifecycle:

1. DECLARE (Create): Define the cursor by giving it a name and associating it with a SELECT statement.
2. OPEN: Execute the query, populate the result set, and position the cursor before the first row.
3. FETCH: Retrieve the current row the cursor is pointing to and advance the cursor to the next row. This is usually performed inside a loop.
4. CLOSE: Release the active result set and the resources used by the cursor.

---

A Complete Practical Example

Let's imagine a scenario where we need to give a 5% salary raise to all employees in the 'Sales' department who currently earn less than $60,000. We want to process each eligible employee one by one and print a confirmation message for each.

Step 1: Create a Sample Table

First, let's create and populate a simple employees table.

`sql
CREATE TABLE employees (

employee_id   NUMBER PRIMARY KEY,
full_name     VARCHAR2(100),
department    VARCHAR2(50),
salary        NUMBER(10, 2)

);

INSERT INTO employees VALUES (101, 'John Smith', 'Sales', 55000);
INSERT INTO employees VALUES (102, 'Jane Doe', 'Marketing', 65000);
INSERT INTO employees VALUES (103, 'Peter Jones', 'Sales', 70000);
INSERT INTO employees VALUES (104, 'Mary Williams', 'Sales', 58000);
COMMIT;
`

Step 2: The PL/SQL Program with a Cursor

This PL/SQL block will use a cursor to find the eligible employees and process them one by one.

`sql
-- Enable output in tools like SQL*Plus or SQL Developer
SET SERVEROUTPUT ON;

-- Start of the PL/SQL block
DECLARE

-- Variables to hold the data fetched from the cursor.
-- Using %TYPE is a good practice to match the table's column types.
v_emp_id    employees.employee_id%TYPE;
v_full_name employees.full_name%TYPE;
v_salary    employees.salary%TYPE;

-- 1. DECLARE the cursor
CURSOR c_sales_employees IS
    SELECT employee_id, full_name, salary
    FROM employees
    WHERE department = 'Sales'
      AND salary < 60000;

BEGIN

-- 2. OPEN the cursor to execute the query
OPEN c_sales_employees;

-- Start a loop to process each row
LOOP
    -- 3. FETCH data from the current row into our variables
    FETCH c_sales_employees INTO v_emp_id, v_full_name, v_salary;

    -- Exit the loop if the last fetch returned no more rows.
    -- c_sales_employees%NOTFOUND is a cursor attribute that becomes TRUE
    -- when the FETCH statement fails to find a row.
    EXIT WHEN c_sales_employees%NOTFOUND;

    -- Now, process the data for the current employee
    DBMS_OUTPUT.PUT_LINE('Processing ' || v_full_name || ' with current salary: ' || v_salary);

    -- (Here you would typically perform an UPDATE, but for this example, we will just print)
    -- UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_emp_id;
    
    DBMS_OUTPUT.PUT_LINE('  -> Gave 5% raise.');

END LOOP;

-- 4. CLOSE the cursor to release resources
CLOSE c_sales_employees;

DBMS_OUTPUT.PUT_LINE('--- Process complete. ---');

EXCEPTION

WHEN OTHERS THEN
    -- It's good practice to ensure the cursor is closed even if an error occurs
    IF c_sales_employees%ISOPEN THEN
        CLOSE c_sales_employees;
    END IF;
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');

END;
/
`

Expected Output:

`
Processing John Smith with current salary: 55000
-> Gave 5% raise.
Processing Mary Williams with current salary: 58000
-> Gave 5% raise.
--- Process complete. ---

PL/SQL procedure successfully completed.
`

Step-by-Step Explanation of the Example Code

1. DECLARE Section:
   We declare variables (v_emp_id, v_full_name, v_salary) to temporarily store the data for the single employee we are currently processing.
   CURSOR c_sales_employees IS ...;: We define our cursor named c_sales_employees and link it to a SELECT statement that finds all employees in 'Sales' earning less than $60,000.

2. OPEN c_sales_employees;:
   * This command executes the query. The database identifies two employees (John Smith and Mary Williams) and creates a result set in memory. The cursor's pointer is positioned before the first record (John Smith).

3. LOOP ... END LOOP;:
   We use a loop to iterate through the result set.
   First Iteration:

   *   `FETCH c_sales_employees INTO ...;`: The cursor retrieves the data for John Smith and populates the `v_emp_id`, `v_full_name`, and `v_salary` variables. The pointer then moves to the next record (Mary Williams).
   *   `EXIT WHEN c_sales_employees%NOTFOUND;`: This condition is false, so the loop continues.
   *   The `DBMS_OUTPUT` lines are executed for John Smith.
   

   • Second Iteration:
     • FETCH ...;: The cursor retrieves the data for Mary Williams and populates the variables. The pointer moves past the last record.
     • EXIT WHEN ...;: This condition is still false.
     • The DBMS_OUTPUT lines are executed for Mary Williams.
   • Third Iteration:
     • FETCH ...;: The fetch command tries to get another row, but there are no more. It fails to retrieve data.
     • EXIT WHEN c_sales_employees%NOTFOUND;: The %NOTFOUND attribute is now TRUE, so the EXIT command is executed, and the loop terminates.

4. CLOSE c_sales_employees;:
   * This command releases the memory and resources that were allocated to the cursor's result set. This is a crucial step for good resource management.