Requirements and Options for Host Languages › Using SQL in a PL/I Application Program › Defining Host Variables › Defining Bulk Structures

Defining Bulk Structures

A bulk structure is a group element or a record which contains a subordinate array for holding multiple occurrences of input or output values. Bulk structures are used in bulk SELECT, INSERT, and FETCH statements for retrieving or storing multiple rows of data.

Format of a Bulk Structure

A bulk structure consists of three levels:

• The highest level is the structure itself (level 01 through 253)
• The second level is a multiply-occurring group item (level 02 through 254)
• The third level consists of elementary or variable length data items

  The number, type and order of data items at the lowest level must correspond to the number, data type, and order of column values being retrieved or inserted.

Bulk Structure Example

The following is an example of a valid bulk structure:

EXEC SQL  BEGIN DECLARE SECTION;
DCL  1  BULK_DATA,
     4  BULK_ROW (20),
     5  EMP_ID  FIXED DECIMAL(3),
     5  EMP_NAME  CHAR(30),
     5  DEPT_NAME CHAR(30);
EXEC SQL  END DECLARE SECTION;

Referring to a Bulk Structure

When referring to a bulk structure in a SELECT, FETCH, or INSERT statement, the name of the highest level is used:

EXEC SQL
  FETCH EMPCURS BULK :BULK_DATA;

Indicator Variables

An indicator variable can be associated with a data item within the structure as follows:

• The indicator variable must immediately follow the data item with which it is associated
• The data type of the indicator variable must be SQLIND

  On encountering the SQLIND data type, the precompiler interprets the variable as an indicator associated with the preceding variable. SQLIND is replaced with BINARY FIXED(31) in the generated source.

Restrictions

A subscripted data element may not appear within the lowest level of a bulk structure.

Using INCLUDE TABLE

A bulk structure can be defined for a given table by using the INCLUDE TABLE statement with a NUMBER OF ROWS clause. The statement in this example will generate a bulk structure capable of holding 20 entries:

EXEC SQL
  INCLUDE TABLE EMPLOYEE NUMBER OF ROWS 20;