"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:
“But I am not tragically colored. There is no great sorrow dammed up in my soul, nor lurking behind my eyes. I do not mind at all. I do not belong to the sobbing school of negrohood who hold that nature somehow has given them a lowdown dirty deal.... No, I do not weep at the world—I am too busy sharpening my oyster knife.”
—Zora Neale Hurston (1907–1960)
“Some hours seem not to be occasion for any deed, but for resolves to draw breath in. We do not directly go about the execution of the purpose that thrills us, but shut our doors behind us and ramble with prepared mind, as if the half were already done. Our resolution is taking root or hold on the earth then, as seeds first send a shoot downward which is fed by their own albumen, ere they send one upward to the light.”
—Henry David Thoreau (1817–1862)