Using Cursors
To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor.
- Declare a cursor.
EXEC SQL DECLARE c CURSOR FOR select * from tb1;
- Open a cursor.
EXEC SQL OPEN c;
- Fetch a row of data from a cursor.
EXEC SQL FETCH 1 in c into :a, :str;
- 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; }
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot