Was this helpful?
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. In other cases, such as when database updates are required, or when other tables need to be browsed 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.
Within the select loop, no other statements that access the database can be issued. This will cause a runtime error. To see how to manipulate and update rows and tables within the database while data is being retrieved, see Data Manipulation with Cursors on page 10 in the chapter “Embedded OpenSQL.”
However, if your program is connected to multiple database sessions, queries can be issued 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 goto statement. For more information about multiple sessions, see the chapter "OpenSQL Features.”
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 statement must be syntactically within the BEGIN-END block that delimits the select loop. For more information, see ENDSELECT.
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;
Last modified date: 11/28/2023