General rules
Types and functions of the data manipulation SQL
The data manipulation SQL performs operations on table data (retrieving, adding, deleting, and updating).
The following figure shows 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. |