Entering Data into Tables
The following sections explain how to perform cursor-independent operations and update and delete using cursors.
The SQL statements
UPDATE, as well user-defined function invocations, embedded in application programs operate on a set of rows in a table or view in exactly the same way as in interactive SQL.
Host variables may be used in the statements to supply values or set search conditions. Host variables may be used as routine parameters.
Examples:EXEC SQL INSERT INTO mimer_store.items(item_id, product_id, format_id, release_date, price, stock, reorder_level, ean_code, producer_id) VALUES (CURRENT_VALUE OF mimer_store.item_id_seq, :product_id, :format_id, mimer_store.cast_to_date(:book_release_date), :book_price, :book_stock, :book_reorder_level, (:ean * 10) + mimer_store.ean_check_digit(:ean), producer_id);
From the standpoint of the application program, each statement is a single indivisible operation, regardless of how many columns and rows are affected.
Updating and Deleting Through Cursors
DELETE CURRENTstatements, see the Mimer SQL Reference Manual, SQL Statement Descriptions, for the syntax description, allow update and delete operations respectively, to be controlled on a row-by-row basis from an application program. These statements operate through cursors, which are declared and opened as described above for
These statements operate on the current row of the cursor referenced in the statement. If there is no current row, e.g. the cursor has been opened but not yet positioned with a
FETCHstatement, an error is raised.
UPDATE CURRENTchanges the content of the current row according to the
SETclause in the statement, but does not change the position of the cursor. Two consecutive
UPDATE CURRENTstatements will therefore update the same row twice.
DELETE CURRENTdeletes the current row and does not move the cursor; after a
DELETE CURRENTstatement, the cursor is positioned between rows and there is no current row. The cursor must be moved to the next row with a
FETCHstatement before any other operation can be performed through the cursor.
DELETE CURRENTstatements, the table name as used in the statement must be exactly the same as the table name addressed in the cursor declaration. The cursor must also address an updatable result set.
FOR UPDATE OFclause is used to specify which fetched columns may be updated, only the columns specified may appear in the corresponding
DELETE CURRENTchanges for a particular cursor can be divided into several transactions if the cursor is a holdable cursor. A cursor declared
WITH HOLDremains open when transactions are committed, which makes it possible to use the same cursor for fetch and update of additional rows after
COMMIT. However, each row must still be fetched and updated (or deleted) in the same transaction.
Cursors are not updatable if the data retrieval statement in the cursor declaration contains any of the following features at the top level (i.e. not in a subselect) of the statement:
- reference to more than one table in the
- reference to a read-only view in the
- the keyword
- set-functions in the
- arithmetic or string concatenation expressions in the
- the result set of an explicit inner or outer
CALLto a result set procedure
When to Use UPDATE CURRENT, DELETE CURRENT
DELETE CURRENTstatements are useful for manipulating single rows in interactive applications where rows are displayed, and the user decides which rows to delete or update.
The example below illustrates the program framework for such an operation (the construction is similar for a
DELETE CURRENToperation):... EXEC SQL DECLARE c_1 CURSOR FOR ... ; ... EXEC SQL OPEN c_1; EXEC SQL WHENEVER NOT FOUND GOTO done; loop EXEC SQL FETCH c_1 INTO :VAR1, :VAR2, ..., :VARn; display VAR1, VAR2, ..., VARn; prompt "Update this row?"; if ANSWER = "YES" then prompt "Give new values"; EXEC SQL UPDATE tab SET col1 = :NEWVAL1, col2 = :NEWVAL2, ... WHERE CURRENT OF c_1; display "Row updated"; end if; prompt "Display next row?"; exit when ANSWER = "NO"; end loop; done: EXEC SQL CLOSE c_1;
In situations where there is no requirement to interactively choose rows and where all the rows to be updated or deleted can be specified completely in terms of a
WHEREclause, it is more efficient to do so rather than use a cursor.
An operation completely specified as a
WHEREclause is executed as a single statement, rather than a series of statements (i.e. one for each
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40