Insert with a cursor

The code in the previous example (Declare an insert cursor) prepares an insert cursor for use. The continuation, as the following example shows, demonstrates how the cursor can be used. For simplicity, this example assumes that a function named next_cust returns either information about a new customer or null data to signal the end of input.

EXEC SQL BEGIN WORK;
EXEC SQL OPEN new_custs;
while(SQLCODE == 0)
{
   next_cust();
   if(the_company == NULL)
      break;
   EXEC SQL PUT new_custs;
}
if(SQLCODE == 0)                    /* if no problem with PUT */
{
   EXEC SQL FLUSH new_custs;        /* write any rows left */
   if(SQLCODE == 0)                 /* if no problem with FLUSH */
      EXEC SQL COMMIT WORK;         /* commit changes */
}
else
   EXEC SQL ROLLBACK WORK;          /* else undo changes */

The code in this example calls next_cust repeatedly. When it returns non-null data, the PUT statement sends the returned data to the row buffer. When the buffer fills, the rows it contains are automatically sent to the database server. The loop normally ends when next_cust has no more data to return. Then the FLUSH statement writes any rows that remain in the buffer, after which the transaction terminates.

Re-examine the information about the INSERT statement. See The INSERT statement. The statement by itself, not part of a cursor definition, inserts a single row into the customer table. In fact, the whole apparatus of the insert cursor can be dropped from the example code, and the INSERT statement can be written into the code where the PUT statement now stands. The difference is that an insert cursor causes a program to run somewhat faster.