SQL was designed to work with a set of data and SELECT statement is the most important statement. SQL statement allows us to select a record or a set of records based on some criteria and it solves almost problem in database management. But in some database management contexts, we need to traverse through a set or records, process each record, move to next record and loop until the final record is reached. This is the reason why database cursors has invented.
There are many types of cursors are implemented in various RDBMS products using different syntax. Even though, the way we work with cursors are following this fashion:
- We have to define a cursor with and set its features
- Then we must populate a set of records to use with the cursor
- We must set the position for cursor to a record or set of records that we need to traverse through
- Data from one or more current records is fetched, then we can make some modification on it.
- Loop the step 3 and 4 until the cursor reaches the final record in the set of record
- Finally we have to close and release the cursor resources.
In this tutorial, we will use Transact-SQL cursors for demonstration. We also provide you resources if you work with other RDBMS such as MySQL, Oracle... Transact-SQL cursor can be used from stored procedure, functions or triggers. Let's move to the section which we will show you how to use Transact-SQL cursors.