HP 3000 Manuals HP 3000 Manuals
BULK Table Processing
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.