使用游标

使用游标可以检索出多行的结果集，应用程序必须声明一个游标并且从游标中抓取每一行数据。

1. 声明一个游标：

   EXEC SQL DECLARE c CURSOR FOR select * from tb1;

2. 打开游标：

   EXEC SQL OPEN c;

3. 从游标中抓取一行数据：

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

4. 关闭游标：

   EXEC SQL CLOSE c;

在开启GUC参数enable_ecpg_cursor_duplicate_operation（默认开启）的情况下，使用ECPG连接ORA兼容数据库时，允许通过如下方式重复打开/关闭游标：

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

更多游标的使用细节请参见DECLARE，关于FETCH命令的细节请参见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;
    /* 提前创建testdb */
    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;

    // 定义游标
    exec sql declare c cursor for select * from tb1;
    // 打开游标
    exec sql open c;
    exec sql whenever not found do break;
    while(1) {
        // 抓取数据
        exec sql fetch 1 in c into :a, :str;
        count++;
        if (count == 100000) {
            printf("Fetch res: a is %d, str is %s", *a, str);
        }
    }
    // 关闭游标
    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：指定游标的名称。

当cursor指向表的某一行时，可以使用此语法更新或删除cursor当前指向的行。使用限制及约束请参考UPDATE章节对此语法介绍。

完整使用示例：

#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;
    /* 提前创建好testdb */
    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;
    /* 打开游标 */
    exec sql open cur1;
    exec sql fetch 1 in cur1 into :va, :vb;
    printf("c1:%d, c2:%d\n", va, vb);
    /* 使用where current of删除当前行 */
    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);
    /* 使用where current of更新当前行 */
    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);
    /* 关闭游标 */
    exec sql close cur1;
    exec sql set autocommit to on;
    exec sql drop table t1;
    exec sql disconnect;

    ECPGfree_auto_mem();
    return 0;

}