By default, when a procedure exits, all cursors that were opened in a procedure are closed. Closing cursors means that cursors are left in a prepared state and can be re-executed.
After exiting, the procedure is put in the procedure cache. When the procedure is dropped from the cache, all cursors are dropped.
The number of procedures kept in the cache is determined by the SQL.ProcedureCache parameter, see SQL section.
This means that, as long as the procedure is in the procedure cache, all cursors can be reused as long as they are not explicitly dropped. A solidDB server manages the procedure cache by keeping track of the cursors declared, and notices if the statement that a cursor contains has been prepared.
As cursor management, especially in a heavy multi-user environment, can use a considerable amount of server resources, it is good practice to close cursors immediately and drop all cursors that are no longer used. Only the most frequently-used cursors should be left closed (but not dropped) to reduce the cursor preparation effort.
Note that transactions are not related to procedures or other statements; committing or rolling back transactions does not release any resources in a procedure.
Notes
▪ Cursors can be declared anywhere in a stored procedure. Cursors that are definitely going to be used are best prepared directly following the DECLARE section.
▪ Cursors that are used inside control structures, and are therefore not always necessary, are best declared at the point where they are activated, to limit the amount of open cursors and hence the memory usage.
▪ The cursor name is an undeclared identifier, not a variable; it is used only to reference the query. You cannot assign values to a cursor name or use the cursor name in an expression.
▪ Cursors can be re-executed repeatedly without having to re-prepare them. Note that this can have a serious influence on performance; repetitively preparing cursors on similar statements can decrease the performance by around 40% in comparison to re-executing already prepared cursors.