"WITH HOLD"
Cursors are usually closed automatically at the end of a transaction, i.e. when a COMMIT or ROLLBACK (or an implicit termination of the transaction) occurs. That behavior can be changed if the cursor is declared using the WITH HOLD clause. (The default is WITHOUT HOLD.) A holdable cursor is kept open over COMMIT and closed upon ROLLBACK. (Some DBMS deviate from this standard behavior and also keep holdable cursors open over ROLLBACK.)
DECLARE cursor_name CURSOR WITH HOLD FOR SELECT ... FROM ...When a COMMIT occurs, a holdable cursor is positioned before the next row. Thus, a positioned UPDATE or positioned DELETE statement will only succeed after a FETCH operation occurred first in the transaction.
Note that JDBC defines cursors as holdable per default. This is done because JDBC also activates auto-commit per default. Due to the usual overhead associated with auto-commit and holdable cursors, both features should be explicitly deactivated at the connection level.
Read more about this topic: Cursor (databases)
Famous quotes containing the word hold:
“My ambition for station was always easily controlled. If the place came to me it was welcome. But it never seemed to me worth seeking at the cost of self-respect, or independence. My family were not historic; they were well-to-do, did not hold or seek office. It was easy for me to be contented in private life. An honor was no honor to me, if obtained by my own seeking.”
—Rutherford Birchard Hayes (1822–1893)
“You expect far too much of a first sentence. Think of it as analogous to a good country breakfast: what we want is something simple, but nourishing to the imagination. Hold the philosophy, hold the adjectives, just give us a plain subject and verb and perhaps a wholesome, nonfattening adverb or two.”
—Larry McMurtry (b. 1936)