The EXECUTE statement executes a dynamically-compiled SQL statement other than SELECT. You can use this statement only in SQL that is embedded in a program.
None required.
►►─ EXECUTE statement-name ───────────────────────────────────────────────────► ►─┬──────────────────────────────────────────────────────────────┬───────────►◄ │ ┌─────── , ─────────────┐ │ └─ USING ─┬─▼┬─ host-variable ─────┬┴────────────────────────┬─┘ │ ├─ local-variable ────┤ │ │ └─ routine-parameter ─┘ │ ├─ :dyn-buff sql DESCRIPTOR descriptor-area-name ──┤ ├─ BULK :bulk-buffer bulk-options ─────────────────┤ └─ BULK :dyn-buff dynamic-bulk-options1 ───────────┘
Expansion of bulk-options
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name ─┘ ►──┬─────────────────────────────────┬───────────────────────────────────────►◄ └─ ROWS :row-count-variable-name ─┘
Expansion of dynamic-bulk-options1
►►──┬──────────────────────────────┬──────────────────────────────────────────► └─ START :start-variable-name ─┘ ►── ROWS :row-count-variable-name ───────────────────────────────────────────► ►── sql DESCRIPTOR descriptor-area-name ─────────────────────────────────────►◄
Identifies the statement being executed.
For detailed information, see Expansion of Statement-name.
Supplies values for the dynamic parameters embedded in the text of the statement.
Identifies the host variables from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first host variable to the first dynamic parameter, the second host variable to the second dynamic parameter, and so on.
You must specify the same number of host variables in the USING parameter as the number of dynamic parameter markers in the statement text.
Note: In COBOL, host-variable can be an elementary data item or a non-bulk structure. If a non-bulk structure is specified, each sub-element of the structure is counted as a host variable.
Note: For detailed information, see Expansion of Host-variable.
Identifies the local variable or routine parameter from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first local variable or routine parameter to the first dynamic parameter, the second local variable or routine parameter to the second dynamic parameter, and so on. You must specify the same number of local variables and routine parameters in the USING parameter as the number of dynamic parameter markers in the statement text.
Identifies the variable or bulk-buffer from which CA IDMS is to retrieve values for the dynamic parameters.
Dyn-buff must identify a variable previously declared in the host-language application program or SQL routine.
The size of dyn-buff must be sufficient to hold a complete set of dynamic parameter values for a single execution of the statement. If specified as part of the BULK parameter, dyn-buff must be sufficient to hold row-count-variable sets of dynamic parameters. The format of the data in dyn-buff must conform to the description in the SQL descriptor area specified by descriptor-area-name
Directs CA IDMS to execute the statement one or more times and to use a contiguous storage area to retrieve input values for the dynamic parameters. The specification of BULK is a CA IDMS extension of the SQL Standard.
Note: BULK may only be specified if the statement being executed is an INSERT statement.
Identifies a variable from which CA IDMS is to retrieve one or more sets of input values. Bulk-buffer must identify a variable previously declared in the host-language application program or SQL routine.
Bulk-buffer must be defined as a multiple-occurring structure having the same number of sub-elements as there are dynamic parameters in the statement.
Optionally specify the location in bulk-buffer for the first row and the number of rows to be inserted. Expanded syntax for bulk-options immediately follows the statement syntax.
Provides specification for inserting one or more rows into a table.
Expanded syntax for dynamic-bulk-options1 appears immediately following the expanded syntax for bulk-options. Descriptions of dynamic-bulk-options1 parameters appear above.
Note: dyn-buff, bulk-buffer, start-variable-name, and row-count-variable-name are variables that can be host variables or when the statement is used in an SQL routine local variables or routine parameters. In this case, their names must not be preceded with a colon.
Parameters for Expansion of bulk-options
Identifies a variable containing the relative position within the bulk buffer from which CA IDMS is to retrieve values for the first row to be inserted. Values in subsequent entries in the bulk buffer are retrieved sequentially, each set corresponding to a row to be inserted.
Start-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the variable must be an integer in the natural range of subscripts for arrays in the language in which the application program is written.
If you do not specify the START parameter, CA IDMS retrieves the values from the first entry in the bulk buffer.
Identifies a variable that specifies the number of rows CA IDMS is to retrieve from the bulk buffer.
Row-count-variable-name must be a variable previously declared in the host-language application program or SQL routine. The value in the variable must be in the range 1 through the number of rows that will fit in the bulk buffer.
If you do not specify the ROWS parameter, CA IDMS retrieves rows from the array sequentially until reaching the end of the buffer.
Parameters for Expansion of dynamic-bulk-options1
An additional parameter is used with dynamic-bulk-options1.
Specifies the SQL descriptor area that describes the format of the dynamic parameter values contained in dyn-buff.
Directs CA IDMS to use the named area as the descriptor area. Descriptor-area-name must identify an SQL descriptor area.
Dynamically-compiled SELECT Statements
You cannot use the EXECUTE statement with a dynamically-compiled SELECT statement. To retrieve data using a dynamically-compiled SELECT statement, you must define a cursor and use the FETCH statement.
Use of the Descriptor Area
When describing the format of dynamic parameters with an SQL descriptor area, you can use the INPUT option of the DESCRIBE statement to determine the format of the parameters that CA IDMS has assumed based on the context in which they appear. You can alter the contents of the descriptor area provided that the data types remain compatible. However, all changes to the descriptor area must be made before the first time the EXECUTE statement for the given dynamically-compiled statement is executed. The contents of the descriptor area must remain unchanged for each subsequent execution.
Executing a Dynamically-compiled Statement
The following EXECUTE statement executes the dynamically-compiled statement named DYN_PROJ:
EXEC SQL EXECUTE DYN_PROJ END EXEC
|
Copyright © 2014 CA.
All rights reserved.
|
|