A database cursor is something like an indicator that you can use to hover on a specific row within a result of the inquiry. In a sense, it is a pointer that indicates the current row. You can move this indicator from one row to another, and depending on the type of cursor use, you can even move it back to the previous row.

A database cursor may be used in the following situation:

  • Statements that give back more than a single row of data coming from the database server. For instance, a statement like EXECUTE FUNCTION needs to utilize a function cursor while a statement indicating SELECT needs a select cursor.
  • Statements indicating INSERT which actually transmits more than a single row of data towards the database server. This statement needs an insert cursor.

Types of Cursors to Use

Cursors go in two main categories: cursors for scrolling ability and cursors with skills for detecting changes made into the database.

There are two main scrolling abilities:

  • SCROLL

It is a cursor that can use operations which include FIRST, PRIOR, RELATIVE, LAST, NEXT and ABSOLUTE in order to steer the results.

  • FORWARD_ONLY

This kind of cursor begins at the first row and ends on the last row. In simpler terms, this cursor can only move to the next row of results.

Database Sensitivity

In terms of the ability to detect changes in the database, there is a term called database sensitivity. Database sensitivity has different elements.

  • KEYSET

This cursor can view the changes that are actually made on the rows which are initially into cursor since these keys (unique row identifiers) are utilized in order to obtain rows during a cursor operation. This kind of cursor cannot view the rows that are added to the table.

  • STATIC

The changes made will not mirror in the cursor’s result. The changes made in the cursor’s data are also not permanent and are not stored in the underlying database tables

  • DYNAMIC

This is the complete opposite of STATIC as the changes made will be mirrored in the cursors.

Now that you know the basics of database cursors, you will surely know which type of cursors to choose for specific purposes.

Leave a Reply

Your email address will not be published. Required fields are marked *