 |
» |
|
|
|
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
in the paragraph named DISPLAY-TABLE.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PARTSTABLE.
05 TABLE-ELEMENT OCCURS 10 TIMES.
10 PARTNUMBER PIC X(16).
10 PARTNAME PIC X(30).
10 PARTNAMEIND SQLIND.
01 STARTINDEX PIC S9(9) COMP.
01 NUMBEROFROWS PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
01 OK PIC S9(9) COMP VALUE 0.
01 NOTFOUND PIC S9(9) COMP VALUE 100.
01 I PIC S9(9) COMP.
01 MAXIMUMROWS PIC S9(9) COMP VALUE 10.
.
.
.
PROCEDURE DIVISION.
EXEC SQL DECLARE PARTSCURSOR
CURSOR FOR
SELECT PARTNUMBER, PARTNAME
FROM PURCHDB.PARTS
END-EXEC.
.
.
.
EXEC SQL OPEN PARTSCURSOR END-EXEC.
IF SQLCODE = OK
PERFORM DISPLAY-TABLE THRU DISPLAY-TABLE-EXIT
UNTIL SQLCODE = NOTFOUND
ELSE
IF SQLCODE = NOTFOUND
DISPLAY "The PurchDB.Parts table is empty!"
ELSE
PERFORM SQL-STATUS-CHECK.
DISPLAY-TABLE.
The STARTINDEX and NUMBEROFROWS host variables
are initialized, then the BULK FETCH command is
executed.
MOVE 1 TO STARTINDEX.
MOVE MAXIMUMROWS TO NUMBEROFROWS.
EXEC SQL BULK FETCH PARTSCURSOR
INTO :PARTSTABLE,
:STARTINDEX,
:NUMBEROFROWS
END-EXEC.
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.
IF SQLCODE = OK
PERFORM DISPLAY-ROW VARYING I FROM 1 BY 1
UNTIL I = SQLERRD(3)
ELSE
IF SQLCODE = NOTFOUND
DISPLAY "No more rows qualify!"
ELSE
PERFORM SQL-STATUS-CHECK.
DISPLAY-TABLE-EXIT.
DISPLAY-ROW.
This paragraph displays all the rows returned
to the PARTSTABLE array during the last BULK FETCH.
|
BULK table processing is discussed in additional detail
in Chapter 9.
|
