Menu
Leaving cursor open is known as a bad practice.
271
Not deallocating AFAIK only has to do with performance. The aforementioned resources will remain allocated and thus have a negative effect on server performance.
If you open such a cursor, it will stay open until you CLOSE it explicitly or you disconnect your Oracle session.
DEALLOCATE removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed. Scroll locks used to protect the isolation of fetches are freed at DEALLOCATE .
Just to add: if you use the cursor stored in local variable declared somewhere in the script (e.g. DECLARE @cursorName CURSOR ) use appropriate cursor type ('variable') when getting status: CURSOR_STATUS('variable','@cursorName'). deallocating a cursor automatically closes it.
This freeing is not the same as closing the cursor -- if a cursor is fully deallocated but not closed, the locks the cursor held (if any) remain in place until the end of the transaction, per the docs.
CLOSEand DEALLOCATEserve different purposes. CLOSEleaves the cursor available for reopening later; DEALLOCATEdecreases the reference count and causes the cursor to be freed when the last reference is gone.
However, in the CLOSE docs, nothing is mentioned that the cursor is closed when a cursor variable goes out of scope.
The aforementioned resources will remain allocated and thus have a negative effect on server performance. allocated resources from (open or closed, but non deallocated) cursors will remain allocated until the session (or connection) is closed Thanks for contributing an answer to Stack Overflow!
It depends on whether you declared the cursor locally or globally (and what the default is in your environment - default is global but you can change it).
If the cursor is global, then it can stay "alive" in SQL Server until the last piece of code is touched in the scope in which it was created. For example if you call a stored procedure that creates a global cursor, then call 20 other stored procedures, the cursor will live on while those other 20 stored procedures are running, until the caller goes out of scope. I believe it will stay alive at the session level, not the connection level, but haven't tested this thoroughly. If the cursor is declared as local, then it should only stay in scope for the current object (but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm).
The general concept, though, should be: when you're done with something, say so.
In order to make my cursors as efficient as possible, I always use the following declarations:
DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR SELECT ... I've also heard that there can be memory issues if you only CLOSE or only DEALLOCATE so I always do both when I'm done:
CLOSE c; DEALLOCATE c; However how many cursors do you have where cleaning up this syntax is an issue? If you have hundreds of cursors in your system, that is certainly a red flag to me.
EDIT
As an addendum, I just want to clarify that cursors in and of themselves are not bad. They are often misused and abused, though - implemented in cases where a more efficient, set-based solution could have been implemented, but the person tasked with writing the query could only think procedurally. A few cases where cursors make sense:
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
