Cursor

#docslug#/ecob/ecob/V1.1.0/cursor OceanBase Embedded SQL in C (ECOB) V1.1.0 supports the following cursor-related statements: DECLARE CURSOR, OPEN, FETCH, and CLOSE.

DECLARE CURSOR

You can execute the DECLARE CURSOR statement to declare a cursor variable. In ECOB V1.1.0, the cursor variable only supports queries.

Syntax:

EXEC SQL DECLARE <cur_name> [SCROLL] CURSOR [WITH HOLD] FOR (select_stat | :host_variable | prepare_id)

The SCROLL parameter specifies the cursor as a scroll cursor. When MODE is set to ANSI, the cursor will be closed when the COMMIT statement is executed. You can use the WITH HOLD keyword in the command to prevent the cursor from being closed when the COMMIT statement is executed. By default, MODE is set to Oracle.

Sample statement:

char * sql_stat = "select c1,c2 from t1";
EXEC SQL PREPARE stat from :sql_stat;
EXEC SQL DECLARE cur CURSOR for stat;

OPEN

You can execute the OPEN statement to open a cursor variable.

Syntax:

EXEC SQL OPEN <cur_name> [ USING :host_variable ]

Sample statement:

int c2val = 0;
char * sql_stat = "select c1,c2 from t1";
EXEC SQL PREPARE stat from :sql_stat;
EXEC SQL DECLARE cur CURSOR for stat;
EXEC SQL OPEN cur;
EXEC SQL DECLARE cur1 SCROLL CURSOR for select c1 from t1 where c2 > :c2;
EXEC SQL OPEN cur1 USING :c2val;

FETCH

You can execute the FETCH statement to get the result set stored by the cursor variable. For a scroll cursor, you can specify the position to FETCH the scroll cursor.

Syntax:

EXEC SQL FETCH [ABSOLUTE (:host_variable | n )] [NEXT] <cur_name>  INTO :host_variables [ [INDICATOR] :indicator_variable]...

The ABSOLUTE parameter specifies the cursor position and can be followed by a host variable or numeric constant. You can use the NEXT keyword to push the cursor forward. At present, you can only push the cursor forward, instead of pushing it backward.

Sample statement:

int c2val = 0;
int c1val = 0;
int pos = 2;
short ind;
EXEC SQL DECLARE cur1 SCROLL CURSOR for select c1 from t1 where c2 > :c2;
EXEC SQL OPEN cur1 USING :c2val;
EXEC SQL FETCH NEXT cur1 INTO :c1val:ind;
EXEC SQL FETCH ABSOLUTE :pos INTO :c1val:ind;
EXEC SQL FETCH ABSOLUTE 3 INTO :c1val;
EXEC SQL CLOSE cur1;

CLOSE

The CLOSE statement is used to close a cursor variable.

Syntax:

EXEC SQL CLOSE <cur_name>

Sample statement:

int c1val;
EXEC SQL CREATE TABLE t1 (c1 int);
EXEC SQL INSERT INTO t1 VALUES(1);
EXEC SQL COMMIT WORK;
EXEC SQL DECLARE cur CURSOR FOR select c1 from t1;
EXEC SQL OPEN cur;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
for(;;){
EXEC SQL FETCH cur into :c1val;
}
EXEC SQL CLOSE cur;