The “overhead” with cursors is merely part of the API. Cursors are how parts of the RDBMS work under the hood. Often CREATE TABLE and INSERT have SELECT statements, and the implementation is the obvious internal cursor implementation.
Using higher-level “set-based operators” bundles the cursor results into a single result set, meaning less API back-and-forth.
Cursors predate modern languages that provide first-class collections. Old C, COBOL, Fortran, etc., had to process rows one at a time because there was no notion of “collection” that could be used widely. Java, C#, Python, etc., have first-class list structures to contain result sets.
The Slow Issue
In some circles, the relational joins are a mystery, and folks will write nested cursors rather than a simple join. I’ve seen truly epic nested loop operations written out as lots and lots of cursors. Defeating an RDBMS optimization. And running really slowly.
Simple SQL rewrites to replace nested cursor loops with joins and a single, flat cursor loop can make programs run in 100th the time. [They thought I was the god of optimization. All I did was replace nested loops with joins. Still used cursors.]
This confusion often leads to an indictment of cursors. However, it isn’t the cursor, it’s the misuse of the cursor that’s the problem.
The Size Issue
For really epic result sets (i.e., dumping a table to a file), cursors are essential. The set-based operations can’t materialize really large result sets as a single collection in memory.
Alternatives
I try to use an ORM layer as much as possible. But that has two purposes. First, the cursors are managed by the ORM component. Second, the SQL is separated from the application into a configuration file. It’s not that the cursors are bad. It’s that coding all those opens, closes and fetches is not value-add programming.