5. Working with Embedded SQL : Data Manipulation with Cursors : Cursors Versus Select Loops
 
Share this page                  
Cursors Versus Select Loops
A select loop is a block of code associated with an embedded SELECT statement; the select loop processes the rows returned by the select. Select loops are typically used when the select returns more than one row.
The select loop is an enhancement of standard SQL. ANSI SQL does not allow more than one row to be retrieved by a SELECT statement. If multiple rows are to be retrieved, the ANSI standard requires the use of a cursor even if the rows are not updated.
Cursors enable an application to retrieve and manipulate individual rows without the restriction of the select loop. Within a select loop, statements cannot be issued that access the database. Use cursors in the following situations:
When a program needs to scan a table to update or delete rows.
When a program requires access to other tables while processing rows.
When more than one table needs to be scanned simultaneously (parallel queries).
When more than one table needs to be scanned in a nested fashion, for example, in a master-detail application.
The following two examples do the same thing. The first example uses a select loop and the second uses a cursor. Because there are no nested updates and only one result table is being processed, the select method is preferred.
//Select Loop Version
exec sql select ename, eno, sal
    into :name, :empnum, :salary
    from employee
    order by ename;

    exec sql begin;
    print name, salary, empnum;
    exec sql end;
 
//Cursor Version

exec sql declare c1 cursor for
    select ename, eno, sal/* No INTO clause */
    from employee
    order by ename;

    exec sql open c1;
    exec sql whenever not found goto closec1;

    loop while more rows
    exec sql fetch c1 into :name, :salary, :empnum;
    print name, salary, empnum;
    end loop;

    closec1:
    exec sql close c1;