 |
» |
|
|
|
BULK table processing offers a way to retrieve or insert
multiple rows with the execution of a single SQL command. Three
commands can be used in this fashion: You can use the BULK SELECT command when you know in advance
the maximum number of rows in a multiple-row query result, as when the
query result will contain a row for each month of the year or day of
the week. This command minimizes the time a table is
locked for the retrieval operation, because the program can execute
the BULK SELECT command, then immediately terminate the transaction,
even before displaying any rows.
You can use the BULK FETCH command to handle multiple-row query
results of unpredictable maximum length. This use of a cursor
is most suitable for display only applications, such as programs that
let a user browse through a query result, so many rows at a time.
You can use the BULK INSERT command to insert multiple rows
into a table. Like the BULK SELECT command, this command is efficient
for concurrency, because any exclusive lock acquired to insert rows need
be held only until the BULK INSERT command is executed.
In each of these three commands, the host variables
that hold rows are in
an array, as illustrated in
the following example. The example shows how you can use
a cursor to retrieve and display ten
rows at a time from the active set. The host variable
named StartIndex
is set to 1 so that the first row in each group of rows fetched
is stored in the first element of the PartsTable array.
The host variable named
NumberOfRows controls the
maximum number of rows returned with each execution of the BULK
FETCH command. StartIndex and NumberOfRows are set
before the first BULK FETCH is executed.
const
OK = 0;
NotFound = 100;
MaximumRows = 10;
var
EXEC SQL BEGIN DECLARE SECTION;
PartsTable : packed array[1..10] of
packed record
PartNumber : packed array[1..16] of char;
PartName : packed array[1..30] of char;
PartNameInd : sqlInd;
StartIndex : integer;
NumberOfRows : integer;
EXEC SQL END DECLARE SECTION;
.
.
.
procedure DeclareCursor;
begin
EXEC SQL DECLARE PartsCursor
CURSOR FOR
SELECT PartNumber, PartName
FROM PurchDB.Parts;
.
.
.
function OpenCursor: boolean;
begin
EXEC SQL OPEN PartsCursor;
if SQLCA.SQLCODE <> OK then
begin
OpenCursor := FALSE;
SqlStatusCheck;
ReleaseDBE;
end
else
OpenCursor := TRUE;
end;
.
.
.
procedure GetRows;
begin
if OpenCursor then
begin
StartIndex := 1;
NumberOfRows := MaximumRows;
while SQLCA.SQLCODE = OK do
begin
EXEC SQL BULK FETCH PartsCursor
INTO :PartsTable,
:StartIndex,
:NumberOfRows;
As many as ten rows are put into the PartsTable
array. If the FETCH command executes without
error the value in SQLERRD(3) indicates the
number of rows returned to PartsTable.
case SQLCA.SQLCODE of
OK : for i := 1 to sqlca.sqlerrd(3) do
writeln(Partnumber,'|',PartName);
NotFound : writeln ('No more rows qualify!');
otherwise : SqlStatusCheck;
end; (*case*)
end; (*do*)
end; (*if*)
|
BULK table processing is discussed in additional detail
in the chapter, "Bulk Table Processing." |
