Updated on 2024-05-07 GMT+08:00

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.

  • If JDBC is used to call a stored procedure whose returned value is a cursor, the returned cursor cannot be used.
  • In distributed mode, if the parameter of the stored procedure called in the SQL statement is of the refcursor type or the return value is of the refcursor type, pushdown is not supported.
  • 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.

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

Table 1 Cursor usage conditions

SQL Statement

Cursor

Non-query statements

Implicit

Query statements with single-line results

Implicit or explicit

Query statements with multi-line results

Explicit