8. SQL Statements : SELECT (embedded) : Non-Cursor Select : Select Loops
 
Share this page                  
Select Loops
A select loop can be used to read a table and process its rows individually. When a program needs to read a table without issuing any other database statements during the retrieval (such as for report generation), use a select loop. If other tables must be queried while the current retrieval is in progress, use a cursor.
The BEGIN-END statements delimit the statements in the select loop. The code is executed once for each row as it is returned from the database. Statements cannot be placed between the SELECT statement and the BEGIN statement.
During the execution of the select loop, no other statements that access the database can be issued because this causes a runtime error. For information about manipulating and updating rows and tables within the database while data is being retrieved, see the chapter “Working with Embedded SQL.”
However, if your program is connected to multiple database sessions, you can issue queries from within the select loop by switching to another session. To return to the outer select loop, switch back to the session in which the SELECT statement was issued.
To avoid preprocessor errors, the nested queries cannot be within the syntactic scope of the loop but must be referenced by a subroutine call or some form of a go to statement.
There are two ways to terminate the select loop: run it to completion or issue the endselect statement. A host language go to statement cannot be used to exit or return to the select loop.
To terminate a select loop before all rows are retrieved the application must issue the ENDSELECT statement. The ENDSELECT (see page ENDSELECT) statement must be syntactically within the BEGIN-END block that delimits the select loop.
The following example retrieves a set of rows from the database:
EXEC SQL SELECT ename, sal, eno
       INTO :ename, :sal, :eno
       FROM employee
       ORDER BY eno;
EXEC SQL BEGIN;
       browse data;
       if error condition then
              exec sql endselect;
       end if;
EXEC SQL END;