A bulk fetch is a FETCH statement that retrieves multiple rows from a cursor into a host variable array.
To execute a bulk fetch:
Note: For more information about the FETCH statement, see the CA IDMS SQL Reference Guide.
Cursor Position
The first execution of a FETCH BULK statement retrieves the first set of rows from the cursor result table. After statement execution, cursor position is on the last row fetched. If the FETCH BULK statement is executed again before the cursor is closed, the next set of rows retrieved begins with the row following the cursor position. Fetching proceeds sequentially through the cursor result table until no more rows are found.
How Many Rows Are Fetched?
If you do not specify a ROWS parameter in the BULK clause, the FETCH statement retrieves as many rows as will fit between the starting row of the array and the end of the array.
If you specify a ROWS parameter in the BULK clause, the FETCH statement retrieves a number of rows equal to the value in the ROWS. This value must be less than or equal to the number of rows between the starting row of the array and the end of the array.
Maximum Rows Example
In this example, the program assigns a ROWS value that corresponds to the number of rows that can be displayed on a given display terminal:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 BULK-DIVISION. 02 BULK-DIV OCCURS 100 TIMES. 03 DEPT-ID PIC 9(4). 03 DEPT-NAME PIC X(40). 01 DIV-CODE PIC X(3). 01 WS-SCREEN-LENGTH PIC S9(4) COMP. . . . EXEC SQL DECLARE DIV_DEPT CURSOR FOR SELECT DEPT_ID, DEPT_NAME FROM DEPARTMENT WHERE DIV_CODE = :DIV-CODE END-EXEC. ACCEPT SCREENSIZE INTO WS-SCREEN-LENGTH. SUBTRACT 4 FROM WS-SCREEN-LENGTH. IF WS-SCREEN-LENGTH > 100 MOVE 100 TO WS-SCREEN LENGTH. . . . MOVE INPUT-DIV-CODE TO DIV-CODE. EXEC SQL OPEN DIV_DEPT END-EXEC. FETCH-PARAGRAPH. EXEC SQL FETCH DIV_DEPT BULK :BULK-DIVISION ROWS :WS-SCREEN-LENGTH END-EXEC. IF SQLCODE=100 MOVE 'Y' TO END-FETCH. . . . (Iterate paragraph until no more rows)
Specifying a Starting Row
The DBMS assigns the first row of the result table to the first row of the array unless you include the START parameter on the BULK clause. The START value corresponds to the subscript value of the array occurrence.
Checking Statement Execution
If program logic calls for repeating the FETCH BULK statement until no more rows are found, the program must test for SQLCODE = 100, as described in Using a Cursor. The DBMS always sets the value of SQLCNRP equal to the number of rows returned unless an error occurs during processing.
The following table shows the possible combination of values returned to SQLCODE and SQLCNRP on a FETCH BULK statement:
SQLCODE and SQLCNRP Values
Result of bulk fetch |
SQLCODE value |
SQLCNRP value |
---|---|---|
No rows are returned |
100 |
0 |
At least one row is returned but fewer rows than the maximum allowed |
100 |
Equals the number of rows returned |
The number of rows returned matches the maximum allowed |
0 |
Equals the number of rows returned |
Advantages of a Bulk Fetch
Using a BULK clause with a FETCH statement minimizes resources to retrieve data.
Unlike a bulk select, the program can retrieve an unlimited number of result rows by repeating a bulk fetch.
Bulk Fetch Considerations
Bulk Fetch Example
In this example, the program issues an INCLUDE TABLE statement to declare a host variable array for several columns of the EMPLOYEE table. Then it declares a cursor to select the column values from all rows of the table.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE TABLE EMPLOYEE AS BULK-EMPLOYEE (EMP_ID, EMP_FNAME, EMP_LNAME, DEPT_ID) NUMBER OF ROWS 50 PREFIX 'BULK-' END-EXEC. EXEC SQL END DECLARE SECTION END-EXEC. . . . EXEC SQL DECLARE EMP_CRSR CURSOR FOR SELECT EMP_ID, EMP_FNAME, EMP_LNAME, DEPT_ID ORDER BY 4, 3, 2 END-EXEC.
When the FETCH statement is executed, the first 50 rows of the cursor result table are assigned to the BULK-EMPLOYEE array, because the default starting row assignment is 1 and the default number of rows assigned is the array size. If the FETCH statement is repeated, the next 50 rows of the result table are assigned to the array.
EXEC SQL OPEN EMP_CRSR END-EXEC. . . . EXEC SQL FETCH EMP_CRSR BULK :BULK-EMPLOYEE END-EXEC. IF SQLCODE = 100 MOVE 'Y' TO END-FETCH.
Copyright © 2013 CA.
All rights reserved.
|
|