Overview

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers pointing to context regions. With cursors, stored procedures can control alterations in context regions.

• When a stored procedure uses returns to return a cursor, if JDBC is used to call the stored procedure, the returned cursor is unavailable. Using out to output parameters is not affected.
• When a stored procedure contains COMMIT/ROLLBACK, an explicit cursor caches all data of the cursor to ensure that the cursor is still available after COMMIT/ROLLBACK. If the cursor data volume is large, this process may take a long time.
• After table data is modified in a stored procedure, the cursor related to the table is started, and the data in the FETCH cursor continues after rollback. In this case, an error is reported.
• When a cursor in a PACKAGE modifies data, if the transaction is rolled back due to an exception, the cursor can no longer be executed.

Cursors are classified into explicit cursors and implicit cursors. Table 1 shows the usage conditions of explicit and implicit cursors for different SQL statements.