Entering Data into Tables
The following sections explain how to perform cursor-independent operations and update and delete using cursors.
The SQL statements CALL, INSERT, DELETE and 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
The UPDATE CURRENT and DELETE CURRENT statements, 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 FETCH.
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 FETCH statement, an error is raised.
UPDATE CURRENT changes the content of the current row according to the SET clause in the statement, but does not change the position of the cursor. Two consecutive UPDATE CURRENT statements will therefore update the same row twice.
DELETE CURRENT deletes the current row and does not move the cursor; after a DELETE CURRENT statement, the cursor is positioned between rows and there is no current row. The cursor must be moved to the next row with a FETCH statement before any other operation can be performed through the cursor.
For both UPDATE CURRENT and DELETE CURRENT statements, 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.
If a FOR UPDATE OF clause is used to specify which fetched columns may be updated, only the columns specified may appear in the corresponding UPDATE statement.
UPDATE CURRENT and DELETE CURRENT changes for a particular cursor can be divided into several transactions if the cursor is a holdable cursor. A cursor declared WITH HOLD remains 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 FROM clause
- reference to a read-only view in the FROM clause
- the keyword DISTINCT
- set-functions in the SELECT list (AVG, COUNT, MAX, MIN, SUM)
- arithmetic or string concatenation expressions in the SELECT list
- a GROUP BY clause
- an ORDER BY clause
- the UNION keyword
- the result set of an explicit inner or outer JOIN
- a CALL to a result set procedure
When to Use UPDATE CURRENT, DELETE CURRENT
UPDATE CURRENT and DELETE CURRENT statements 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 CURRENT operation):... 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 WHERE clause, it is more efficient to do so rather than use a cursor.
An operation completely specified as a WHERE clause is executed as a single statement, rather than a series of statements (i.e. one for each FETCH etc.).
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40