Cursor in DBMS

For example

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

-- Open the cursor
OPEN emp_cursor;

-- Fetch rows from the cursor
FETCH NEXT FROM emp_cursor;


-- Process the fetched row
-- ...


-- Close the cursor
CLOSE emp_cursor;

In this example, we declare a cursor named `emp_cursor` that retrieves the `employee_id`, `first_name`, and `last_name` columns from the `employees` table. We then open the cursor, fetch the rows one by one using the `FETCH` statement, process each fetched row as needed, and finally close the cursor.

Cursors provide a powerful way to handle and manipulate result sets in a DBMS. They allow you to work with individual rows, apply complex processing logic, and perform operations that may not be possible with simple queries.

Implicit Cursors

In DBMS, an implicit cursor is a type of cursor that is automatically created and managed by the database system itself. Unlike explicit cursors, which require you to explicitly declare and manage them, implicit cursors are generated behind the scenes whenever you execute certain SQL statements.

When you execute a DML (Data Manipulation Language) statement such as INSERT, UPDATE, or DELETE, or a SELECT statement that returns only one row, the database system automatically creates an implicit cursor to handle the operation. The implicit cursor is opened, the statement is executed, and then the cursor is closed automatically, all without any explicit intervention from the programmer.

Implicit cursors are useful for retrieving information about the execution of SQL statements. They provide access to various cursor attributes that hold information such as the number of rows affected by the statement, the last generated ID value (if applicable), and the status of the last executed statement.

Let’s see some commonly used implicit cursor attributes:

1. `SQL%FOUND`: Returns TRUE if at least one row was affected by the last SQL statement, and FALSE otherwise.
 

2. `SQL%NOTFOUND`: Returns TRUE if no rows were affected by the last SQL statement, and FALSE otherwise.
 

3. `SQL%ROWCOUNT`: Returns the number of rows affected by the last SQL statement.
 

4. `SQL%ISOPEN`: Always returns FALSE for implicit cursors because they are automatically opened and closed.
 

These attributes allow you to check the status of the last executed SQL statement and retrieve information about the affected rows.

For example : 

SET SERVEROUTPUT ON;

DECLARE

  CURSOR emp_cursor IS

    SELECT employee_id, first_name, last_name

    FROM employees;

  v_employee_id employees.employee_id%TYPE;

  v_first_name employees.first_name%TYPE;

  v_last_name employees.last_name%TYPE;

BEGIN

  OPEN emp_cursor;

  LOOP

    FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', First Name: ' || v_first_name || ', Last Name: ' || v_last_name);

  END LOOP;

  CLOSE emp_cursor;

END;

/

Output

In this example, we execute an UPDATE statement to increase the salary of employees in the 'HR' department by 10%. After the statement is executed, we use the `SQL%FOUND` attribute to check if any rows were affected. If rows were updated, we use the `SQL%ROWCOUNT` attribute to retrieve the number of affected rows and display it using `DBMS_OUTPUT.PUT_LINE`. If no rows were updated, we display a message indicating that no rows were affected.

Implicit cursors simplify the process of handling single-row operations and retrieving information about the execution status of SQL statements. They are automatically managed by the database system, reducing the need for explicit cursor management in certain scenarios.

Explicit Cursors

Explicit cursors are user-defined cursors that allow you to manually control the retrieval of rows from a result set. Unlike implicit cursors, which are automatically managed by the database system, explicit cursors require you to explicitly declare, open, fetch rows, and close them.

Explicit cursors are useful when you need to process a result set containing multiple rows and perform operations on each row individually. They provide more control and flexibility compared to implicit cursors.

Let’s see, how explicit cursor works : 

1. Declare the cursor:

   - Specify the name of the cursor and the SELECT statement that defines the result set.

   - The SELECT statement can include parameters for dynamic queries.
 

2. Open the cursor:

   - Execute the SELECT statement associated with the cursor.

   - Allocate memory for the result set.
 

3. Fetch rows from the cursor:

   - Retrieve the rows one by one from the result set.

   - Use a loop construct (e.g., LOOP, WHILE, FOR) to fetch rows sequentially.

   - Access the fetched row using the cursor attributes.
 

4. Process the fetched row:

   - Perform the desired operations on the fetched row.

   - Apply any business logic or calculations.

   - Update or delete the row if needed.
 

5. Close the cursor:

   - Release the memory allocated for the result set.
 

   - Terminate the cursor's association with the result set.

For example : 

SET SERVEROUTPUT ON;

DECLARE

    CURSOR emp_cursor IS

        SELECT employee_id, first_name, last_name, salary

        FROM employees_new

        WHERE department = 'Sales';

    

    emp_rec emp_cursor%ROWTYPE;

BEGIN

    -- Open the cursor

    OPEN emp_cursor;

    

    -- Fetch rows from the cursor

    LOOP

        FETCH emp_cursor INTO emp_rec;

        EXIT WHEN emp_cursor%NOTFOUND;        

        -- Process the fetched row

        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id);

        DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);

        DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_rec.salary);

        DBMS_OUTPUT.PUT_LINE('---');

    END LOOP;

    

    -- Close the cursor

    CLOSE emp_cursor;

END;

/

Output

In this example, we declare an explicit cursor named `emp_cursor` that retrieves the `employee_id`, `first_name`, `last_name`, and `salary` columns from the `employees` table for employees in the 'Sales' department.

We open the cursor using the `OPEN` statement, which executes the SELECT statement associated with the cursor.

Inside a loop, we fetch rows from the cursor one by one using the `FETCH` statement. The fetched row is stored in the `emp_rec` variable, which is declared with the `emp_cursor%ROWTYPE` attribute to match the structure of the cursor's result set.

We process each fetched row by displaying the employee details using `DBMS_OUTPUT.PUT_LINE`. The loop continues until there are no more rows to fetch, which is determined by the `emp_cursor%NOTFOUND` attribute.

Finally, we close the cursor using the `CLOSE` statement to release the allocated resources.

Explicit cursors provide more control over the processing of result sets compared to implicit cursors. They allow you to iterate over multiple rows, perform complex calculations, and apply custom logic to each fetched row.

Frequently Asked Questions

Can cursors be used with any SQL statement?

Cursors are mainly used with SELECT statements that return multiple rows. They are not used with single-row SELECT statements or DML statements like INSERT, UPDATE, or DELETE.

What is the difference between implicit and explicit cursors?

Implicit cursors are automatically created and managed by the database system for single-row operations, while explicit cursors are user-defined and allow manual control over the retrieval and processing of multiple rows from a result set.

Are cursors mandatory to use in database programming?

Cursors are not mandatory, but they are useful when you need to process result sets containing multiple rows and perform operations on each row individually. They provide more control and flexibility compared to simple queries.

Conclusion

In this article, we learned about cursors in DBMS and their usage in database programming. We explained what cursors are, how they act as pointers to specific rows within a result set, and how they allow row-by-row processing. We discussed the two types of cursors: implicit cursors, which are automatically managed by the database system, and explicit cursors, which require manual declaration and management. Implicit cursors are used for single-row operations and provide information about the execution status of SQL statements, while explicit cursors are used for processing multiple rows and offer more control and flexibility.

You can also practice coding questions commonly asked in interviews on Coding Ninjas Code360. 

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, System Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.