Pranav Bakare

Posted on Sep 23 • Edited on Sep 28

PL/SQL Cursors

#sql #backend #database #fullstack

Cursors in PL/SQL

In PL/SQL, cursors are used to handle multiple rows returned by queries in a controlled manner. A cursor acts like a pointer to the result set of a SQL query, allowing you to retrieve and process each row one at a time. Cursors come in two types - implicit and explicit depending on the level of control you want to exercise over fetching rows.

.
.
.

Implicit Cursors

Definition: Implicit cursors are automatically created and managed by Oracle whenever an SQL statement (like SELECT INTO, INSERT, UPDATE, or DELETE) is executed. These are used when the SQL statement affects a single row or the query is expected to return exactly one row. Implicit cursors do not require explicit handling or management, and PL/SQL handles the opening, fetching, and closing of the cursor automatically.

Key Points:

No need for explicit cursor handling (no need to declare, open, fetch, or close).
Automatically created when DML statements are executed.

Example of Implicit Cursor:

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
-- Implicit cursor is used here 
-- because the SELECT statement returns a single row
    SELECT first_name || ' ' || last_name
    INTO v_employee_name
    FROM employees
    WHERE employee_id = 101;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/

Explanation:

The SELECT INTO statement automatically creates an implicit cursor.
The INTO clause stores the retrieved employee’s name into the variable v_employee_name.
Since the query is guaranteed to return only one row (based on the WHERE condition), an implicit cursor is sufficient here.

.
.
.

Explicit Cursors

Definition: Explicit cursors are defined and managed by the developer to handle queries that return multiple rows. With explicit cursors, the programmer has control over opening the cursor, fetching rows, and closing the cursor. These cursors are useful when you need to loop through the result set row by row.

Key Points:

You must explicitly declare, open, fetch from, and close the cursor.
Useful for queries returning multiple rows.
Provides fine control over the fetched data.

Steps for Using Explicit Cursors:

Declare the Cursor: Define the SQL query that the cursor will execute.
Open the Cursor: Execute the query and initialize the result set.
Fetch Rows: Retrieve rows one by one from the result set.
Close the Cursor: Release the memory and resources used by the cursor.

Example of Explicit Cursor:

DECLARE
    -- Declare the cursor for selecting employee details
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 10;

    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 to execute the query
    OPEN emp_cursor;

    -- Fetch rows one by one using a loop
    LOOP
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;

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

        -- Output employee details for each row fetched
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name);
    END LOOP;

    -- Close the cursor to release resources
    CLOSE emp_cursor;
END;
/

Explanation:

Declare: The cursor emp_cursor is declared to fetch employee_id, first_name, and last_name from the employees table where the department_id is 10.

Open: The OPEN statement executes the query and prepares the result set for fetching.

Fetch: The FETCH statement retrieves one row at a time into the variables v_employee_id, v_first_name, and v_last_name.

Loop: The loop continues fetching rows until the EXIT WHEN emp_cursor%NOTFOUND condition is true, meaning no more rows are available.

Close: The CLOSE statement releases the resources associated with the cursor.

.
.
.

Cursor Attributes

Cursor attributes help monitor the status of the cursor during its lifecycle. They provide valuable information about the result set and the current position within it.

Important Cursor Attributes:

1. %FOUND: Returns TRUE if the last FETCH was successful (i.e., if a row was fetched), and FALSE otherwise.

2. %NOTFOUND: Returns TRUE if the last FETCH was unsuccessful (i.e., no more rows to fetch), and FALSE otherwise.

3. %ROWCOUNT: Returns the number of rows fetched so far by the cursor.

4. %ISOPEN: Returns TRUE if the cursor is currently open and FALSE if it is closed.

Example Using Cursor Attributes:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id FROM employees WHERE department_id = 10;
    v_employee_id employees.employee_id%TYPE;
BEGIN
    -- Open the cursor
    OPEN emp_cursor;

    LOOP
        -- Fetch each row
        FETCH emp_cursor INTO v_employee_id;

        -- Exit loop if no more rows
        EXIT WHEN emp_cursor%NOTFOUND;

        -- Using %ROWCOUNT attribute to get the number of rows processed
        DBMS_OUTPUT.PUT_LINE('Processed Row: ' || emp_cursor%ROWCOUNT || ' Employee ID: ' || v_employee_id);
    END LOOP;

    -- Close the cursor
    CLOSE emp_cursor;
END;
/

Explanation:

The %ROWCOUNT attribute tracks the number of rows processed, printing the count after each row is fetched.

The loop terminates when emp_cursor%NOTFOUND becomes TRUE, meaning no more rows are available.

.
.
.

Types of Explicit Cursors

In PL/SQL, explicit cursors can be classified into a few specialized types based on their functionality:

1. Parameterized Cursors:

Cursors that accept parameters, allowing you to pass values dynamically into the query.

Example of Parameterized Cursor:

DECLARE
    CURSOR emp_cursor (p_dept_id NUMBER) IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = p_dept_id;

    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 with a specific department ID
    OPEN emp_cursor(10);

    LOOP
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;

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

    CLOSE emp_cursor;
END;
/

Explanation:
The cursor emp_cursor is parameterized to accept a p_dept_id value, allowing it to fetch employees based on different department IDs passed during the OPEN statement.

2. FOR Loop Cursor:

A cursor that simplifies fetching by automatically opening, fetching, and closing the cursor.

Example of FOR Loop Cursor:

BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
END;
/

Explanation:
The FOR loop automatically manages the cursor, eliminating the need for OPEN, FETCH, and CLOSE statements. It iterates over each row and processes it.

Key Takeaways:

Implicit Cursors are simple and managed automatically by Oracle, used when queries return a single row.

1. Explicit Cursors provide more control and are ideal for processing multiple rows. Developers manually manage opening, fetching, and closing these cursors.
2. Cursor attributes like %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN help in monitoring the status and managing the flow of cursor operations.
3. Using Parameterized Cursors allows flexibility in querying data with dynamic inputs.
4. A FOR Loop Cursor is a shorthand method that simplifies fetching rows without explicit management.

Mastering these different types of cursors ensures you handle complex query results efficiently and write more robust PL/SQL code.