General rules
Types and functions of the data manipulation SQL
The data manipulation SQL performs operations on table data (retrieving, adding, deleting, and updating).
Table 4-1 lists the types and functions of the data manipulation SQL.
Table 4-1 Types and functions of the data manipulation SQL
Type | Function |
---|---|
ALLOCATE CURSOR statement (Allocate a cursor) | Allocates the cursor to the SELECT statement preprocessed by the PREPARE statement or a group of result sets returned by a procedure. |
ASSIGN LIST statement (Create list) | Creates a list from a base table. |
CALL statement (Call procedure) | Calls a procedure. |
CLOSE statement (Close cursor) | Closes a cursor. |
DEALLOCATE PREPARE statement (Nullify the preprocessing of SQL statements) | Nullifies the SQL statement preprocessed by the PREPARE statement, and releases any allocated SQL statement identifier or extended statement name. |
DECLARE CURSOR (Declare cursor) | Declares a cursor so that the results of a retrieval by the SELECT statement can be fetched row by row with the FETCH statement. |
DELETE statement (Delete rows) | Deletes either the rows that satisfy specified search conditions or the row indicated by a cursor. |
Preparable dynamic DELETE statement: locating (Delete row using a preprocessable cursor) | Deletes the row pointed to by a specified cursor. This command is used for dynamic execution. |
DESCRIBE statement (Receive retrieval information and I/O information) | Returns to the SQL descriptor area retrieval information, output information, or input information on SQL preprocessed by the PREPARE statement. |
DESCRIBE CURSOR statement (Receive cursor retrieval information) | Returns retrieval information for the cursor that references a result set returned by a procedure to the SQL descriptor area. |
DESCRIBE TYPE statement (Receive definition information on a user-defined data type) | Returns to the SQL descriptor area definition information (attribute data codes and data lengths) on a user-defined data type that is directly or indirectly contained in SQL retrieval item information that is preprocessed by a PREPARE statement. |
DROP LIST statement (Delete list) | Deletes a list. |
EXECUTE statement (Execute SQL) | Executes an SQL preprocessed by the PREPARE statement. |
EXECUTE IMMEDIATE statement (Prepare and execute SQL) | Prepares and executes an SQL provided in a character string. |
FETCH statement (Fetch data) | Advances to the next row the cursor that indicates the row to be fetched, and reads column values in that row into the embedded variables specified in the INTO clause. |
FREE LOCATOR statement (Invalidate locator) | Nullifies the locator. |
INSERT statement (Insert rows) | Inserts rows into a table (a row can be inserted by direct specification of values or the SELECT statement can insert one or more rows). |
OPEN statement (Open cursor) | Opens a cursor. Locates the cursor declared in a DECLARE CURSOR statement or a cursor allocated by an ALLOCATE CURSOR statement before the first row of retrieval results so that the retrieval results can be fetched. |
PREPARE statement (Prepare SQL) | Prepares an SQL provided in a character string and assigns a name (SQL statement identifier or extended statement name) to the SQL. |
PURGE TABLE statement (Delete all rows) | Deletes all rows in a base table. |
Single-row SELECT statement (Retrieve one row) | Retrieves table data (fetches only one row of data from a table without using a cursor). |
Dynamic SELECT statement (Retrieve dynamically) | Retrieves table data. The dynamic SELECT statement is preprocessed by the PREPARE statement. After a cursor is declared in a DECLARE CURSOR statement or it is allocated in an ALLOCATE CURSOR statement, the cursor is used to fetch retrieval results row by row. |
UPDATE statement (Update data) | Updates the values of columns in the rows that satisfy specified search conditions or in the row indicated by a cursor. |
Preparable dynamic UPDATE statement: locating (Update data using a preprocessable cursor) | Updates the value of a specified column of the row pointed to by a specified cursor. This command is used for dynamic execution. |
Assignment statement (Assign a value) | Assigns a value to an assign-to item. |
Table 4-2 Remote Database Access in a Distributed System (Data Manipulation SQL)
Data manipulation SQL | Remote database access |
---|---|
ALLOCATE CURSOR statement | N |
ASSIGN LIST statement | N |
CALL statement | Y |
CLOSE statement | Y |
DEALLOCATE PREPARE statement | N |
DECLARE CURSOR | Y* |
DELETE statement | Y |
Preparable dynamic DELETE statement: locating | N |
DESCRIBE statement | Y |
DESCRIBE TYPE statement | N |
DESCRIBE CURSOR statement | N |
DROP LIST statement | N |
EXECUTE statement | Y |
EXECUTE IMMEDIATE statement | Y |
FETCH statement | Y |
FREE LOCATOR statement | N |
INSERT statement | Y |
OPEN statement | Y |
PREPARE statement | Y |
PURGE TABLE statement | Y |
Single-row SELECT statement | Y |
Dynamic SELECT statement | Y |
UPDATE statement | Y |
Preparable dynamic UPDATE statement: locating | N |
Assignment statement | N |