Using Cursors

To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor.

1. Declare a cursor.

   EXEC SQL DECLARE c CURSOR FOR select * from tb1;

2. Open a cursor.

   EXEC SQL OPEN c;

3. Fetch a row of data from a cursor.

   EXEC SQL FETCH 1 in c into :a, :str;

4. Close a cursor.

   EXEC SQL CLOSE c;

When the GUC parameter enable_ecpg_cursor_duplicate_operation is enabled (enabled by default) and ecpg is used to connect to the ORA-compatible database, a cursor can be opened or closed repeatedly in the following ways:

EXEC SQL OPEN c;
EXEC SQL OPEN c;
EXEC SQL CLOSE c;
EXEC SQL CLOSE c;

For details about how to use cursors, see DECLARE. For details about the FETCH command, see FETCH.

#include <string.h>
#include <stdlib.h>

int main(void)
{
exec sql begin declare section;
    int *a = NULL;
    char *str = NULL;
exec sql end declare section;

    int count = 0;
    /* Create testdb in advance. */
    exec sql connect to testdb ;
    exec sql set autocommit to off;
    exec sql begin;
    exec sql drop table if exists tb1;
    exec sql create table tb1(id int, info text);
    exec sql insert into tb1 (id, info) select generate_series(1, 100000), 'test';
    exec sql select count(*) into :a from tb1;
    printf ("a is %d\n", *a);
    exec sql commit;

    // Define a cursor.
    exec sql declare c cursor for select * from tb1;
    // Open the cursor.
    exec sql open c;
    exec sql whenever not found do break;
    while(1) {
        // Capture data.
        exec sql fetch 1 in c into :a, :str;
        count++;
        if (count == 100000) {
            printf("Fetch res: a is %d, str is %s", *a, str);
        }
    }
    // Close the cursor.
    exec sql close c;
    exec sql set autocommit to on;
    exec sql drop table tb1;
    exec sql disconnect;

    ECPGfree_auto_mem();
    return 0;

}

WHERE CURRENT OF cursor_name

cursor_name: specifies the name of a cursor.

When the cursor points to a row in a table, you can use this syntax to update or delete the row. For details about the restrictions, see UPDATE.

A complete example is as follows:

#include <string.h>
#include <stdlib.h>

int main(void)
{
exec sql begin declare section;
    int va;
    int vb;
exec sql end declare section;

    int count = 0;
    /* Create testdb in advance. */
    exec sql connect to testdb ;
    exec sql set autocommit to off;
    exec sql begin;
    exec sql drop table if exists t1;
    exec sql create table t1(c1 int, c2 int);
    exec sql insert into t1 values(generate_series(1,10000), generate_series(1,10000));
    exec sql commit;
    exec sql declare cur1 cursor for select * from t1 where c1 < 100 for update;
    /* Open the cursor. */
    exec sql open cur1;
    exec sql fetch 1 in cur1 into :va, :vb;
    printf("c1:%d, c2:%d\n", va, vb);
    /* Use the WHERE CURRENT OF clause to delete the current line. */
    exec sql delete t1 where current of cur1;
    exec sql fetch 1 in cur1 into :va, :vb;
    exec sql fetch 1 in cur1 into :va, :vb;
    printf("c1:%d, c2:%d\n", va, vb);
    /* Use the WHERE CURRENT OF clause to update the current line. */
    exec sql update t1 set c2 = 21 where current of cur1;
    exec sql select c2 into :vb from t1 where c1 = :va;
    printf("c1:%d, c2:%d\n", va, vb);
    /* Close the cursor. */
    exec sql close cur1;
    exec sql set autocommit to on;
    exec sql drop table t1;
    exec sql disconnect;

    ECPGfree_auto_mem();
    return 0;

}