What is a Cursor in SQL?

A Cursor in SQL is a database object used to retrieve and process rows one at a time from a result set.

In simple words:

A cursor allows row-by-row processing of query results inside the database.

Why Cursors are Important

Normally SQL works with:

Set-based operations

Meaning:

SQL processes all rows together

Example

UPDATE students
SET marks = marks + 10;

This updates:

All rows at once

But Sometimes

Applications need:

Row-by-row processing
Custom logic for each row
Sequential operations

This is where:

Cursor is used

Simple Real-Life Example

Think about:

Teacher checking exam papers one by one

Instead of:

Checking all papers together

Similarly:

Cursor processes database rows one by one.

Cursor Internal Architecture

Execute SQL Query
        |
        v
Result Set Created
        |
        v
Cursor Points to First Row
        |
        v
Process One Row at a Time

How Cursor Works

Cursor performs:

Open cursor
Fetch rows
Process rows
Close cursor

Cursor Lifecycle

DECLARE CURSOR
       |
       v
OPEN CURSOR
       |
       v
FETCH ROWS
       |
       v
PROCESS DATA
       |
       v
CLOSE CURSOR
       |
       v
DEALLOCATE CURSOR

Basic Cursor Syntax

DECLARE cursor_name CURSOR FOR

SELECT columns
FROM table_name;

Cursor Example

Suppose:

Students Table

Student ID	Name	Marks
1	Naresh	80
2	Rahul	70
3	Arjun	90

Requirement

Process:

Each student row individually

Cursor Example Code

DECLARE done INT DEFAULT FALSE;

DECLARE studentName VARCHAR(100);

DECLARE studentMarks INT;

DECLARE studentCursor CURSOR FOR

SELECT name, marks
FROM students;

DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET done = TRUE;

OPEN studentCursor;

read_loop: LOOP

    FETCH studentCursor
    INTO studentName, studentMarks;

    IF done THEN
        LEAVE read_loop;
    END IF;

    SELECT studentName, studentMarks;

END LOOP;

CLOSE studentCursor;

What Happens Internally?

Cursor:

Fetches first row
Processes it
Moves to next row
Repeats until all rows processed

Cursor Query Flow

Run SELECT Query
       |
       v
Store Result Set
       |
       v
Point Cursor to First Row
       |
       v
Fetch Current Row
       |
       v
Move to Next Row
       |
       v
Repeat Until End

Important Cursor Operations

DECLARE
OPEN
FETCH
CLOSE

1. DECLARE Cursor

Defines:

Cursor query

Example

DECLARE studentCursor CURSOR FOR

SELECT name
FROM students;

2. OPEN Cursor

Executes:

Cursor query

Example

OPEN studentCursor;

3. FETCH Cursor

Retrieves:

One row at a time

Example

FETCH studentCursor
INTO studentName;

4. CLOSE Cursor

Releases:

Cursor resources

Example

CLOSE studentCursor;

Why CONTINUE HANDLER is Used

When no more rows exist:

Cursor reaches end of result set

Handler helps:

Stop loop safely

Cursor Visualization

Students Table
----------------

1 -> Naresh
2 -> Rahul
3 -> Arjun

----------------

Cursor Position

[Naresh] -> Rahul -> Arjun

Move Next

Naresh -> [Rahul] -> Arjun

Move Next

Naresh -> Rahul -> [Arjun]

Cursor vs Normal SQL Processing

Feature	Cursor	Normal SQL
Processing	Row by Row	Set Based
Performance	Slower	Faster
Complex Logic	Supported	Limited
Memory Usage	Higher	Lower

Why Cursors are Slower

Because:

Rows processed individually
More database overhead

Set-Based SQL Example

UPDATE employees
SET bonus = bonus + 1000;

Cursor-Based Processing Example

Process each employee individually:

Apply different bonus rules

Real-Time Banking Example

Banking systems use cursors for:

Interest calculation
Transaction processing
Sequential financial operations

Example

Process:

Each customer account individually

Real-Time E-Commerce Example

E-commerce platforms use cursors for:

Bulk order processing
Inventory updates
Shipment generation

Example

Process:

Each pending order one by one

Real-Time Learning Platform Example

Learning platforms use cursors for:

Certificate generation
Student progress calculation
Batch processing

Example

Generate certificates:

One student at a time

Cursors in Microservices

Microservices sometimes use cursors for:

Batch jobs
Large data migrations
Streaming data processing

Example

Process Millions of Records Sequentially

Advantages of Cursors

Supports row-by-row logic
Useful for complex processing
Supports sequential operations
Flexible procedural programming

Disadvantages of Cursors

Slower performance
Higher memory usage
Consumes more resources
Complex code management

Performance Consideration

Cursors should be:

Avoided when set-based SQL is possible

Why?

Because:

Set-based operations are much faster

Best Practices

Use cursors only when necessary
Prefer set-based SQL operations
Always close cursors properly
Optimize cursor queries
Avoid nested cursors

Common Interview Mistake

Many developers think:

Cursors are preferred for all processing

Reality

Cursors should be used:

Only when row-by-row processing is required

Cursor vs Stored Procedure

Feature	Cursor	Stored Procedure
Purpose	Row-by-row processing	Reusable SQL logic
Execution Style	Sequential	Procedural
Performance	Slower	Faster

Professional Interview Answer

A Cursor in SQL is a database object used to process query result rows one at a time. Cursors are useful when row-by-row processing, sequential operations, or complex procedural logic is required. A cursor typically follows the lifecycle of DECLARE, OPEN, FETCH, and CLOSE operations. Although cursors provide flexibility for iterative processing, they are generally slower than set-based SQL operations and should be used only when necessary. Cursors are commonly used in enterprise systems for batch processing, financial calculations, report generation, and large-scale sequential data operations.

Why Interviewers Like This Answer

Clearly explains row-by-row processing
Includes cursor lifecycle understanding
Shows performance trade-off knowledge
Provides enterprise-level examples
Explains practical usage scenarios

Frequently Asked Questions