|
|
|
The non-cursor version of the embedded SQL select statement can be used to retrieve a single row or a set of rows from the database.
If the optional begin-end block syntax is not used, the embedded select statement can retrieve only one row from the database. This kind of select statement is called the singleton select and is compatible with the ANSI standard. If the singleton select does try to retrieve more than one row, an error occurs and the result variables hold information from the first row.
For example, the following example retrieves a single row from the database:
exec sqlselect ename, sal
into :ename, :sal
from employee
where eno = :eno;
|
|
|
Use a select loop 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 becaue 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 goto 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;