1 – ATOMIC
Syntax options:
ATOMIC | NOT ATOMIC
Controls whether or not SQL statements in the compound statement
are undone when any statement in the compound statement
terminates with an exception. Compound statements are NOT ATOMIC
by default.
Most single SQL statements are ATOMIC. Only the control
statements are NOT ATOMIC. For example, an INSERT statement is
ATOMIC, and the entire insert operation either completes or fails
as a unit even if it is contained in a NOT ATOMIC block.
o ATOMIC
In a compound statement defined as ATOMIC, all SQL statements
in a compound statement succeed, or when any of the SQL
statements in the compound statement raises an exception,
they all fail as a unit. Any changes made up to the point
of failure are undone. SQL terminates the compound statement
as soon as a statement within it fails. SQL does not change
variable assignments as a result of a statement failure.
All statements within an ATOMIC block must be atomic. If you
nest compound statements and specify ATOMIC, you must specify
ATOMIC for any inner blocks. If you do not, Oracle Rdb returns
an error.
o NOT ATOMIC (default)
In a compound statement defined as NOT ATOMIC, all SQL
statements that complete successfully up to the point of a
failed statement are not undone as they would be in an ATOMIC
compound statement. Partial success of the statements in a
NOT ATOMIC compound statement can occur, unlike the all-
or-nothing behavior in ATOMIC compound statements. As with
ATOMIC compound statements, NOT ATOMIC compound statements
are terminated when an SQL statement returns an exception. The
partial work of the statement causing a compound statement to
terminate is always undone.
SQL restricts the use of SET TRANSACTION, START TRANSACTION,
COMMIT, and ROLLBACK statements to NOT ATOMIC compound statements
because the nature of an ATOMIC compound statement conflicts with
the properties of these statements. The property of an ATOMIC
block is that all statements succeed, or all statements fail, and
this can not be guaranteed if a transaction is started or ended
during the block.
2 – BEGIN
Begins a compound statement. The END keyword marks the end of
a compound statement. The unit consisting of the BEGIN and END
keywords and all statements bounded by them is called a compound
statement block or just a block. The simplest compound statement
block can consist of BEGIN, END, and a terminating semicolon
(BEGIN END;).
3 – beginning-label:
Assigns a name to a block. You use the label with the LEAVE or
ITERATE statements to perform a controlled exit from a block or
a LOOP statement. Named compound statements are called labeled
compound statements. If a block has an ending label, you must
also supply an identical beginning label. A label must be unique
within the procedure in which the label is contained.
4 – call-statement
Invokes an external or stored procedure. See the CALL Compound_
Statement statement for a complete description.
5 – case-searched-statement
See the CASE_Searched help topic for a complete description.
6 – commit-statement
Ends a transaction and makes any changes that you made during
that transaction permanent. SQL does not allow a COMMIT statement
in an ATOMIC compound statement. The AND CHAIN clause can also be
used to start a new transaction.
See the COMMIT help topic for a complete description.
7 – compound-statement
Lets you nest compound statements in another compound statement.
8 – compound-use-statement
Identifies the SQL statements allowed in a compound statement
block.
9 – CONSTANT
CONSTANT changes the variable into a declared constant that
can not be updated. If you specify CONSTANT, you must also have
specified the DEFAULT clause to ensure the variable has a value.
CONSTANT also indicates that the variable can not be used as
the target of an assignment or be passed as an expression to a
procedure's INOUT or OUT parameter.
10 – control-statement
The set of statements that provide conditional execution,
iterative execution, and cursor-like operations for controlling
the execution flow of SQL statements in a compound statement.
11 – default-clause
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
12 – delete-statement
Deletes a row from a table or view.
See the DELETE help topic for a complete description.
13 – END
Ends a compound statement block.
14 – ending-label
Assigns a name to a block. If a block has a beginning label, you
must use the same name for the ending label.
15 – for-counted-loop-statement
See the FOR (Counted) Control help topic for a complete
description.
16 – for-statement
See the FOR_Control help topic for a complete description.
17 – get-diagnostics-statement
Retrieves diagnostic information for the previously executed
statement.
See the Get_Diagnostics help topic for a complete description.
18 – if-statement
See the IF_Control help topic for a complete description.
19 – insert-statement
Adds a new row, or a number of rows, to a table or view. For
compound statements, SQL restricts the INSERT statement to
database insert operations in a single database.
See the INSERT help topic for a complete description.
20 – leave-statement
See the LEAVE_Control help topic for a complete description.
21 – lock-table-statement
See the LOCK_TABLE help topic for a complete description.
22 – loop-statement
See the LOOP_Control help topic for a complete description.
23 – ON_ALIAS
Specifies an alias allowing your program or interactive SQL
statements to refer to more than one database. Use the same alias
as specified in the ATTACH statement.
SQL> ATTACH 'ALIAS db1 FILENAME mf_personnel';
SQL> ATTACH 'ALIAS db2 FILENAME d1';
SQL> DECLARE :x CHAR(5);
SQL> BEGIN ON ALIAS db1
cont> SELECT EMPLOYEE_ID INTO :x FROM db1.EMPLOYEES
cont> WHERE EMPLOYEE_ID='00164';
cont> END;
SQL> PRINT :x;
X
00164
24 – OPTIMIZE_AS
Assigns a name to the compound statement.
25 – OPTIMIZE_USING
Names the query outline to be used with the compound statement,
even if the outline ID for the query and for the outline are
different.
26 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
27 – PRAGMA
These options may only be specified on the outermost BEGIN
statement. The exception is ATOMIC and NOT ATOMIC.
28 – repeat-statement
See the REPEAT_Control help topic for a complete description.
29 – return-statement
Returns the result for stored functions. See the RETURN_Control
help topic for a complete description.
30 – rollback-statement
Ends a transaction and undoes all changes you made since that
transaction began. SQL does not allow a ROLLBACK statement in an
ATOMIC compound statement. The AND CHAIN clause can also be used
to start a new transaction.
See the ROLLBACK help topic for a complete description.
31 – set-assignment-statement
See the SET_Control help topic for a complete description.
32 – set-transaction-statement
Starts a transaction and specifies its characteristics.
See the SET_TRANSACTION help topic for a complete description.
33 – signal-statement
See the SIGNAL_Control help topic for a complete description.
34 – simple-case-statement
See the CASE_Simple help topic for a complete description.
35 – singleton-select-statement
Specifies a one-row result table.
See the SELECT Singleton_Select statement for a complete
description.
36 – start-transaction-statement
See the START_TRANSACTION help topic for a complete description.
37 – trace-statement
Writes values to the trace log file. See the TRACE_Control help
topic for a complete description.
38 – UPDATABLE
UPDATABLE is the default (versus CONSTANT) and allows the
variable to be modified. An update of a variable can occur due
to a SET assignment, an INTO assignment (as part of an INSERT,
UPDATE, or SELECT statement), or as a procedure's OUT or INOUT
parameter.
39 – update-statement
Modifies a row in a table or view.
See the UPDATE help topic for a complete description.
40 – variable-declaration
Declares local variables for a compound statement. SQL creates
variables when it executes a compound statement and deletes them
when execution of the compound statement ends.
41 – while-statement
See the WHILE_Control help topic for a complete description.
42 – WITH_HOLD
Can be applied to a table cursor so that it remains open across
COMMIT and ROLLBACK actions.