Database Cursors: Row-by-Row Result Processing

WHY IT EXISTS: Databases excel at set-based operations that modify many rows at once. However, some application logic requires processing records individually. Loading a multi-million row result set into application memory is often impossible. Cursors solve this by creating a server-side pointer to the result set, allowing an application to fetch rows one by one or in small batches.

THE MENTAL MODEL: Think of a cursor as a bookmark in a giant book (the query result set). Instead of carrying the whole book, you just keep track of your page. You can read the current line (fetch a row), then move the bookmark to the next line (advance the cursor). This lets you work through the entire result set without holding it all in memory at once.

HOW IT WORKS: An application declares a cursor for a specific SELECT query. The database engine executes the query but instead of sending all the data, it holds the result set on the server and returns a handle to the cursor. The application then uses commands like FETCH to retrieve one or more rows from the cursor's current position. The database manages the state, tracking which row is next. When processing is complete, the application must explicitly CLOSE the cursor to release server resources like locks and memory.

WHEN TO USE IT: Use cursors for procedural, row-by-row processing that cannot be expressed in a single, set-based SQL statement. They are a fit for complex data migration scripts, large batch updates where each row's logic is unique, or when serializing a massive result set to a file or stream.

WHEN NOT TO USE IT: Avoid cursors for any task that can be accomplished with a standard set-based SQL statement like UPDATE, INSERT, or DELETE with a WHERE clause. A single SQL statement is almost always more efficient. Cursors introduce overhead from network round-trips and can hold locks for a long time, which can severely degrade database performance and concurrency for other users.

ONE CANONICAL EXAMPLE: A script needs to deactivate millions of user accounts based on a complex set of rules involving API calls to external systems, which can't be done in SQL. The script declares a cursor for SELECT user_id FROM users WHERE last_login < 'some_date'. It then fetches 1,000 users at a time, processes them in the application code, and issues individual UPDATE statements. This avoids loading millions of IDs into memory.