CURSOR WITH HOLD OPTION

This is an optional specification on CURSOR declaration.The significance  can be understood by considering what happens in its absence.

Suppose we need to process some large table, one row at a time by means of a cursor and update a few of the as we go. It is often desirable to divide the work up into batches and to make the processing of each batch into a separate transaction (by  issuing a separate commit at the end of each one); thus, e.g. a table of one million rows might be processed by a sequence of 10,000 transactions, each one dealing with just 100 rows. This way , for e.g. if it becomes necessary to roll a given transaction back, then at most 100 updates will have to be undone, instead of potentially as many as a million.

The problem with this approach ,however  is that every time we issue a commit, we implicitly close the cursor, thereby losing our position within the table. The first thing each transaction has to do, therefore is to execute some re-positioning code in order to get back to the row that is due to be processed next. And that re-positioning code can often be quite complex, especially if the processing sequence is determined by a combination of several columns.

If the cursor declaration specifies with hold, however, commit does not close the cursor, instead, leaves it open, positioned such that the next FETCH will move it to the next row in sequence. The possibly complex code for repositioning is thus no longer required.

However it is important to note that the first operation on the cursor following the commit must be fetch. Update and delete current are illegal.