Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 262 / 536

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.