Filter
Top Products
Control statements
242
FROM customer
WHERE nationality(id) = ’CDN’
Declarations of cursors and exceptions are discussed in later sections.
Atomic compound statements
An atomic statement is a statement that is executed completely or not at all.
For example, a
LOAD statement that inserts thousands of rows might encounter
an error after many rows. If the statement does not complete, and the default
ON FILE ERROR ROLLBACK option is in effect, all changes are undone. This
LOAD statement is atomic.
All noncompound SQL statements are atomic. A compound statement can be
made atomic by adding the keyword
ATOMIC after the BEGIN keyword.
BEGIN ATOMIC
INSERT INTO
sales_order (id, order_date, sales_rep)
VALUES (41880, 1998-08-24, 2054) ;
INSERT INTO
sales_order_items (line_id, prod_id, quantity,
ship_date)
VALUES (01, 43629, 15, ’bad_data’) ;
END;
In this example, the two INSERT statements are part of an atomic compound
statement. They must either succeed or fail as one. The first
INSERT statement
would succeed. The second one causes a data conversion error since the value
being assigned to the
ship_date column cannot be converted to a date.
The atomic compound statement fails and the effect of both
INSERT statements
is undone. Even if the currently executing transaction is eventually committed,
neither statement in the atomic compound statement takes effect.
COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements are
not permitted within an atomic compound statement. See “Transactions and
savepoints in procedures” on page 265.
There is a case where some, but not all, of the statements within an atomic
compound statement are executed. This is when an error occurs, and is handled
by an exception handler within the compound statement.
For more information, see “Using exception handlers in procedures” on page
261.