The DML (Data Manipulation Language) component of SQL provides the following functionality:
populates the data structures with actual data,
enables the retrieval of data from the data structures,
updates the data by either changing or removing values.
Two distinct concepts are provided for data manipulation, non-cursor and cursor operations.
Statements which are not based on a cursor are not associated with other statements in any way.
Data is generally retrieved by using the SELECT statement. An INTO clause and an appropriate host variable list must be provided in order to receive the returned data. This mechanism, therefore, does not facilitate the retrieval of more than one row. An embedded static SELECT statement may only generate one row, otherwise an exception condition will occur during run time. It is the programmer's responsibility to ensure that the SELECT statement really does only return a single row. It is not possible for this to be checked in any way during the compilation of the statement.
Note: In Interactive SQL or Dynamic SQL there is no such restriction on the number of rows which can be retrieved by a SELECT statement.
The host variable list should match the derived columns list in every aspect. There is a one-to- one correspondence between a derived column and a host variable. The basic type of the host variable must match that of the corresponding derived column. The relative number of items should be the same but need not be. If there are insufficient host variables then data will be lost. If there are too many then the contents of the extra host variables will be undefined after the statement has completed. In either case a compiler warning is issued. Should an error occur during the execution of the query, the values in the host variables are undefined.
Data is inserted into a table using the INSERT statement. The data is inserted on a row by row basis. The source of the data can either be literals or host variables (that is non-SQL derived data) or from a subquery (that is SQL-derived data). When specified using non-SQL data, only one row may be inserted for one execution of the statement. If a subquery is used then as many rows as the subquery provides are inserted for one execution of the statement. The subquery may not access the target table. There is no corresponding cursor-based INSERT statement.
Data can be updated by using the UPDATE statement. The data is updated on a row by row basis as identified by the search expression. Therefore, more than one row can be updated at a time.
Rows of data can be removed from the table by
using the DELETE statement. The data is deleted on a row by row basis
as identified by the search condition and, therefore, many rows can be
deleted at once. If no search condition is specified, then all rows are
identified and the table is cleared of all its data.
Level 1 or level 2 tables cannot be the target of DELETE statements. Data
from such tables can only be removed by deleting the associated level
0 row. In such a case the referencing level 1 and level 2 rows are deleted
automatically with the level 0 row. This is analogous to a DELETE CASCADE
in referential integrity terminology.
As described in the previous section, non-cursor-based statements are not suitable for accessing and updating more than one row in a table. This requires the use of cursors as described below.
A cursor is declared in a DECLARE CURSOR statement along with the underlying query expression. which defines a resultant table. The cursor is used as a pointer to a particular row of this table. At runtime, a static DECLARE CURSOR statement has no effect, it is only a declaration for the SQL compiler.
When the cursor is opened by an OPEN CURSOR statement, the runtime system establishes the resultant table with the cursor pointing to the first row.
Other SQL compilers require that a DECLARE CURSOR statement is followed by the OPEN CURSOR statement. This is because the information contained in the DECLARE CURSOR statement has to be attached to the OPEN statement. Adabas SQL Gateway does not have this restriction. As the static DECLARE CURSOR statement has no effect at runtime, the logical order is not relevant.
Once the cursor has been opened, it can be used by other statements.
Data is retrieved from the resultant table using the FETCH statement. This statement specifies the cursor to be used and a target buffer list which is similar to that of the single row SELECT statement. The target buffer list must match the projection list of the query expression. Each time the FETCH is executed, it moves the cursor to one row and copies the values of the derived columns into the corresponding host variables of the target buffer list. For a newly opened cursor, the first row of the resultant table will be retrieved and the values will be made available to the application program in the host variables. The cursor now points to the first row. Each execution of the FETCH statement results in successive rows of the resultant table being retrieved.
Once all the rows have been fetched, the cursor is said to be exhausted and points past the last row. After the last row has been fetched, the next and any subsequent FETCH statement will result in a return code of +100 being issued by the runtime system. This needs to be checked by the application program either explicitly or by specifying a WHENEVER statement with the NOT FOUND option. Once a cursor is exhausted, it should be closed.
The current row, as determined by the cursor's position in the resultant table, can be updated by using a positioned UPDATE statement. The use of such a statement does not affect the position of the cursor. Only one row, the current one, can be updated by using this statement. However, by embedding the statement in the same loop as the FETCH statement, each row of the resultant table can be updated successively. For this reason, Adabas SQL Gateway permits the use of a FETCH statement without having to specify a target buffer.
Similarly, the current row can be removed from the underlying base table by executing a positioned DELETE statement against the cursor. After execution of the DELETE statement, the row no longer exists, but a FETCH statement must still be executed in order to position the cursor to the next row.
Both the positioned UPDATE and DELETE statements are only valid if it is determined during compilation that the cursor can be updated as specified in SQL Statements.
A cursor can be closed at any time. Normally, it is closed once all rows have been fetched and the cursor is positioned past the last row. Closing the cursor means that the resultant table is discarded along with any internal resources required for the cursor's processing. A cursor is also closed implicitly if a COMMIT or ROLLBACK statement is executed without the KEEPING ALL option.
In general, the FETCH, the positioned UPDATE or DELETE, and the CLOSE statements should appear in the same compilation unit as the associated DECLARE CURSOR statement. This is because all the necessary checks to see if the statement is valid can be performed at compile time. Adabas SQL Gateway, however, does permit such statements to be located in another compilation unit. This is intended to aid the modular design of the application. However, it should be noted that the necessary compile time checks are performed at runtime and may result in a loss of performance.
An application program may contain many DECLARE CURSOR statements but each cursor identifier must be unique. For each DECLARE CURSOR statement there must be at least one OPEN CURSOR statement, and it must be in the same compilation unit. As long as it is not compiled under ANSI compatibility mode, the OPEN CURSOR statement need not follow the DECLARE CURSOR statement. There may also be many instances of the FETCH, positioned UPDATE or DELETE and the CLOSE CURSOR statements. As long as a cursor is closed, either explicitly or implicitly, it may be opened as many times as required.
Closing the cursor does not commit any changes made to the underlying base table. However, these changes are visible to the user, once the cursor is re-opened during the current transaction.