A “cursor”

cursorsdatabase-agnosticterminology

I am new to database concepts and I found some sources that try to explain what is a cursor, but I can't understand what they mean. Can someone explain in a very basic manner what a cursor is and what it does inside a database so that a rookie may be able to understand?
Thanks, guys.

Best Answer

A cursor is essentially a materialized result set from a query that maintains its own state (essentially remembers what row is "current") and in some cases allows you to manipulate the current row.

Cursor lifecycle looks more or less like this:

  1. Open the cursor. At this point the query that defines the cursor is executed.
  2. Position the cursor. After the cursor is opened, it is positioned before the first row of the result set, so one needs to move it to the first row. In the simplest case you can only move the cursor to the "next" row, but in some cases you could go to the "previous" or even to an arbitrary row of the result set, depending on the parameters used to open the cursor and whether the particular DBMS supports these kinds of cursors.
  3. Fetch from the cursor. This operation assigns values from the columns of the "current" row to the application variables.
  4. Update columns in the current row or delete the current row, if the particular cursor allows such manipulation.
  5. Close the cursor. All resources used by the result set are freed at this point.

Typically steps 2-4 are performed in an application program (or stored procedure) loop, allowing you to read each record of the result set and do something with it if necessary.

Related Question