██FR█████ █INTELL███████████

Cursor in Database Management System (DBMS): Types, Properties, and Examples


This content originally appeared on DEV Community and was authored by JHK infotech

In a Database Management System (DBMS), retrieving and manipulating data efficiently is crucial. While SQL queries handle set-based operations well, there are situations where you need to process rows one at a time.
That’s where cursors come in.

In this guide, we’ll cover what is a cursor in DBMS, its properties, types, examples, and best practices for cursor management in SQL.

1. What is Cursor in DBMS?

A cursor in a database is a database object that allows you to fetch, manipulate, and navigate through a set of rows returned by a query, one row at a time.

Definition:

A cursor is a pointer that works as a handle for the rows returned by a query, enabling row-by-row processing in a DBMS.

What is the use of cursor in DBMS?

  • They are useful when your application logic requires processing each row individually.
  • Helpful in complex business logic where set-based SQL queries are insufficient.

Example in real life:
Think of a cursor like a bookmark that helps you move through pages in a book one by one instead of reading the whole book at once.

2. Properties of Cursor in DBMS

Cursors have several key properties that determine how they work:

  1. Scrollability
    • Forward-only: Moves in one direction only.
    • Scrollable: Can move both forward and backward.
  2. Sensitivity
    • Sensitive: Detects changes in the underlying table.
    • Insensitive: Does not detect changes after the cursor is opened.
  3. Holdability
    • WITH HOLD: Cursor remains open after a transaction commit.
    • WITHOUT HOLD: Cursor closes after commit.

3. Types of Cursor in Database Management System

Here are the most common types of cursor in DBMS:

  1. Implicit Cursor – Automatically created by DBMS for single SQL statements (e.g., SELECT).
  2. Explicit Cursor – Defined by the programmer for more control.
  3. Static Cursor – The result set does not change after the cursor is opened.
  4. Dynamic Cursor – Reflects changes in the database while open.
  5. Forward-Only Cursor – Moves in one direction only.
  6. Scrollable Cursor – Can move to any row in the result set.

4. Cursor Management in DBMS and SQL

Cursor management in SQL typically involves these steps:

  1. DECLARE – Define the cursor and the SQL query it will execute.
  2. OPEN – Execute the query and store the result set.
  3. FETCH – Retrieve rows one at a time.
  4. CLOSE – Close the cursor when done.
  5. DEALLOCATE – Release resources used by the cursor.

5. Cursor in DBMS with Example

Here’s an example of an explicit cursor in SQL Server:

DECLARE employee_cursor CURSOR FOR
SELECT employee_id, first_name, salary FROM employees;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @emp_id, @first_name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @first_name + ', Salary: ' + CAST(@salary AS VARCHAR);
    FETCH NEXT FROM employee_cursor INTO @emp_id, @first_name, @salary;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Explanation:

  • The cursor is declared with a SELECT query.
  • It is opened, fetched row by row, and closed when finished.

6. When to Use and When to Avoid Cursors

Use cursors when:

  • You need to perform row-by-row processing.
  • Complex business logic can’t be handled by a single SQL query.

Avoid cursors when:

  • You can use set-based SQL operations, which are faster.
  • The dataset is large — cursors can slow down performance.

7. Advantages and Disadvantages of Cursors

Advantages of Cursors in DBMS

  1. Greater Control Over Row-by-Row Operations

    • Unlike set-based operations, cursors allow you to process one row at a time.
    • This is useful when each row needs individual attention, such as performing calculations, sending updates, or triggering conditional actions.

  2. Ability to Handle Complex Business Logic

    • Some scenarios require conditional checks, loops, and custom rules that cannot be implemented efficiently with a single SQL query.
    • Cursors give you procedural control to handle these cases without creating overly complicated queries.

  3. Real-Time Data Handling

    • With dynamic cursors, changes in the underlying data can be reflected immediately, allowing applications to respond to the most up-to-date information.

  4. Step-by-Step Processing

    • In situations like migration scripts or detailed reporting, cursors can walk through each record systematically, reducing the risk of skipping important data.

Disadvantages of Cursors in DBMS

  1. Slower Performance for Large Datasets

    • Cursors process data row-by-row, which is significantly slower than SQL’s set-based operations that process multiple rows at once.
    • For large result sets, this can cause noticeable delays.

  2. High Resource Consumption

    • Cursors hold data and states in memory while they are open. If not managed well, they can consume large amounts of system resources, leading to performance degradation.

  3. Complexity in Code Maintenance

    • Cursor-based logic often requires more lines of code, making scripts harder to read, maintain, and debug.

  4. Concurrency Issues

    • Long-running cursors can lock database rows or tables, causing delays or blocking other transactions.

8. Common Mistakes in Cursor Management

1. Forgetting to Close and Deallocate Cursors

  • One of the most frequent mistakes is leaving cursors open after use.
  • This can cause memory leaks and prevent other processes from accessing required resources.

  • Always follow these two steps:

    • CLOSE cursor_name;
    • DEALLOCATE cursor_name;

2. Using Cursors When Not Needed

  • Many tasks that are handled with cursors can be solved more efficiently using:

    • JOINs
    • Aggregations (SUM, COUNT, etc.)
    • Window functions

  • Overusing cursors for simple tasks unnecessarily slows down execution.

3. Opening Multiple Cursors Unnecessarily

  • Creating several cursors simultaneously increases memory usage and CPU load.
  • If possible, reuse the same cursor with different queries rather than keeping multiple open.

4. Fetching Too Much Data in One Cursor

  • If the result set is massive, fetching all rows through a cursor can choke performance.
  • Instead, consider breaking the data into smaller batches or using pagination techniques.

5. Ignoring Cursor Type Selection

  • Using a dynamic cursor when a static cursor would suffice can create unnecessary overhead.
  • Choosing the right cursor type based on your needs is key for efficiency.

9. Conclusion

Cursors are a powerful tool in database management systems when used correctly. They allow you to work with rows individually, but they should be used sparingly due to performance considerations.
For cursor management in DBMS, always follow best practices: close and deallocate after use, and prefer set-based operations when possible.

10. FAQs

Q1: What is a cursor in DBMS?

A cursor in a Database Management System is a pointer that allows you to retrieve, navigate, and manipulate rows from a result set one at a time.
Instead of working with all rows together (set-based operations), a cursor enables step-by-step access, which is helpful for complex, procedural operations.
For example, you might use a cursor to loop through each row of an orders table to apply specific business rules before updating inventory.

Q2: What is the use of a cursor in DBMS?

Cursors are mainly used for row-by-row processing in situations where:

  • Complex logic cannot be implemented efficiently in a single SQL statement.
  • Conditional processing is required for each row.
  • You need to sequentially process query results for reporting, migration, or validation.

Example use cases:

  • Calculating custom discounts for each customer order.
  • Sending personalized emails based on individual database records.

Q3: What are the properties of a cursor in DBMS?

Cursors have the following important properties:

  1. Scrollability – Determines whether the cursor can move forward only or both forward and backward.
  2. Sensitivity – Defines whether the cursor reflects changes made to the underlying data while it’s open.
  3. Holdability – Specifies whether a cursor remains open after a transaction commit or closes automatically.

Q4: What are the types of cursors in DBMS?

Cursors can be classified in different ways:

  • Based on declaration:

    • Implicit Cursor – Automatically created by the database for simple queries.
    • Explicit Cursor – Manually declared and controlled by the programmer.

  • Based on movement:

    • Forward-Only Cursor – Can only move forward through the result set.
    • Scrollable Cursor – Can move both forward and backward.

  • Based on data visibility:

    • Static Cursor – Works on a snapshot of data, doesn’t reflect changes.
    • Dynamic Cursor – Reflects changes to the underlying data in real time.

Q5: Can you give a cursor in DBMS with an example?

Yes. Here’s a simple example of an explicit cursor in SQL Server:

DECLARE order_cursor CURSOR FOR
SELECT OrderID, CustomerName
FROM Orders;

OPEN order_cursor;

FETCH NEXT FROM order_cursor INTO @OrderID, @CustomerName;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing order: ' + CAST(@OrderID AS VARCHAR) + ' for ' + @CustomerName;
    FETCH NEXT FROM order_cursor INTO @OrderID, @CustomerName;
END

CLOSE order_cursor;
DEALLOCATE order_cursor;

This example loops through each order, allowing custom processing for each row.

Q6: What is cursor management in SQL?

Cursor management in SQL refers to the steps needed to properly use and maintain a cursor. The typical process includes:

  1. Declare the cursor.
  2. Open the cursor to execute the associated query.
  3. Fetch rows from the cursor one at a time.
  4. Close the cursor when done.
  5. Deallocate the cursor to free up resources.

Proper cursor management prevents memory leaks and ensures database performance remains optimal.


This content originally appeared on DEV Community and was authored by JHK infotech