Allows you to include more than one SQL statement in an SQL
module procedure or in an embedded SQL program. Only by defining
a compound statement can you put multiple SQL statements in
a procedure. Procedures that contain one or more compound
statements are called multistatement procedures.
In contrast, a simple statement can contain a single SQL
statement only. Procedures that contain a single SQL statement
are called simple-statement procedures. See the Simple_Statement
for a description of simple-statement procedures and how you use
them in SQL application programming.
A compound statement and a simple statement differ not just
in the number of SQL statements they can contain. A compound
statement:
o Can include only a subset of the SQL statements allowed in a
simple statement procedure. (See the compound-use-statement
syntax diagram for a list of these valid statements.)
o Can include control flow statements, much like those you can
use in a host language program. (See the control-statement
syntax diagrams for a list of flow control statements allowed
in a compound statement.)
o Can include transaction management statements, such as
ROLLBACK and COMMIT.
o Can include local variables.
o Can control atomicity.
o Can reference only one alias because each compound statement
represents a single Oracle Rdb request.
See the Oracle Rdb Guide to SQL Programming for a conceptual
description of compound statements and their relationship to
multistatement procedures.
1 – Environment
You can use a compound statement:
o In interactive SQL, as a way to test syntax and prototype
compound statements for use with programs.
o In embedded SQL, as part of a host language program to be
processed with the SQL precompiler.
o In SQL module language, as part of a multistatement procedure
in an SQL module file to be processed with the SQL module
processor.
o In dynamic SQL, to prepare and execute compound statements.
2 – Format
compound-statement =
--+-----------------------+-> BEGIN --+--------------------+---+
+-> <beginning-label>: -+ +-> pragma-clauses --+ |
+------------------------------ <------------------------------+
+-+----------------------------+--+
++--> variable-declaration -++ |
+--------------------------+ |
+---------------------------------+
+-+-------------------------------+--> END --+--------------------+-->
++-> compound-use-statement -++ +-> <ending-label>: -+
+---------- <-----------------+
pragma-clauses =
--+-+-> pragma-option --+------------------+--->
| +------- <---------+ |
+-> PRAGMA ( -+-> pragma-option -+-> ) -+
+-------- , <-------+
pragma-option =
--+-> ATOMIC ----------------------+-->
+-> NOT ATOMIC ------------------+
+-> ON ALIAS <alias-name> -------+
+-> with-clause -----------------+
+-> optimize-clause -------------+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
variable-declaration =
---> DECLARE --+-> <variable-name> -+-+--------------+--+
+-------- , <--------+ +-> CONSTANT --+ |
+-> UPDATABLE -+ |
+-------------------------------------------------------+
+-+-> data-type ----+-+-------------------+-+----------------------+->
+-> <domain-name> + +-> default-clause -+ +-> constraint-clause -+
constraint-clause =
--> CHECK --> (search-condition) -+--------------------------+->
+-> constraint-attributes -+
default-clause =
--+-> DEFAULT --+--+--> date-time-literal -+----->
+-> = ------+ +--> interval-literal -+
+--> numeric-literal --+
+--> string-literal ---+
+--> : <variable-name> -+
compound-use-statement =
----+-> call-statement --------------+--> ;
+-> commit-statement ------------+
+-> control-statement -----------+
+-> delete-statement ------------+
+-> get-diagnostics-statement ---+
+-> insert-statement ------------+
+-> lock-table-statement --------+
+-> rollback-statement ----------+
+-> set-transaction-statement ---+
+-> singleton-select-statement --+
+-> start-transaction-statement -+
+-> trace-statement -------------+
+-> update-statement ------------+
control-statement =
---+-> simple-case-statement ------+-->
+-> case-searched-statement ----+
+-> compound-statement ---------+
+-> for-statement --------------+
+-> for-counted-loop-statement -+
+-> if-statement ---------------+
+-> iterate-statement ----------+
+-> leave-statement ------------+
+-> loop-statement -------------+
+-> repeat-statement -----------+
+-> return-statement -----------+
+-> set-assignment-statement ---+
+-> signal-statement -----------+
+-> trace-statement ------------+
+-> while-statement ------------+
3 – Arguments
3.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.
3.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.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.
3.4 – call-statement
Invokes an external or stored procedure. See the CALL Compound_
Statement statement for a complete description.
3.5 – case-searched-statement
See the CASE_Searched help topic for a complete description.
3.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.
3.7 – compound-statement
Lets you nest compound statements in another compound statement.
3.8 – compound-use-statement
Identifies the SQL statements allowed in a compound statement
block.
3.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.
3.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.
3.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.
3.12 – delete-statement
Deletes a row from a table or view.
See the DELETE help topic for a complete description.
3.13 – END
Ends a compound statement block.
3.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.
3.15 – for-counted-loop-statement
See the FOR (Counted) Control help topic for a complete
description.
3.16 – for-statement
See the FOR_Control help topic for a complete description.
3.17 – get-diagnostics-statement
Retrieves diagnostic information for the previously executed
statement.
See the Get_Diagnostics help topic for a complete description.
3.18 – if-statement
See the IF_Control help topic for a complete description.
3.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.
3.20 – leave-statement
See the LEAVE_Control help topic for a complete description.
3.21 – lock-table-statement
See the LOCK_TABLE help topic for a complete description.
3.22 – loop-statement
See the LOOP_Control help topic for a complete description.
3.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
3.24 – OPTIMIZE_AS
Assigns a name to the compound statement.
3.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.
3.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).
3.27 – PRAGMA
These options may only be specified on the outermost BEGIN
statement. The exception is ATOMIC and NOT ATOMIC.
3.28 – repeat-statement
See the REPEAT_Control help topic for a complete description.
3.29 – return-statement
Returns the result for stored functions. See the RETURN_Control
help topic for a complete description.
3.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.
3.31 – set-assignment-statement
See the SET_Control help topic for a complete description.
3.32 – set-transaction-statement
Starts a transaction and specifies its characteristics.
See the SET_TRANSACTION help topic for a complete description.
3.33 – signal-statement
See the SIGNAL_Control help topic for a complete description.
3.34 – simple-case-statement
See the CASE_Simple help topic for a complete description.
3.35 – singleton-select-statement
Specifies a one-row result table.
See the SELECT Singleton_Select statement for a complete
description.
3.36 – start-transaction-statement
See the START_TRANSACTION help topic for a complete description.
3.37 – trace-statement
Writes values to the trace log file. See the TRACE_Control help
topic for a complete description.
3.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.
3.39 – update-statement
Modifies a row in a table or view.
See the UPDATE help topic for a complete description.
3.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.
3.41 – while-statement
See the WHILE_Control help topic for a complete description.
3.42 – WITH_HOLD
Can be applied to a table cursor so that it remains open across
COMMIT and ROLLBACK actions.
4 – Examples
Example 1: Using a compound statement to update rows
The following compound statement uses variables to update rows in
the JOBS table. It uses the SET asssignment control statement to
assign a value to the variable MIN_SAL.
SQL> BEGIN
cont> -- Declare the variable.
cont> DECLARE :MIN_SAL INTEGER(2);
cont> -- Set the value of the variable.
cont> SET :MIN_SAL = (SELECT MIN(MINIMUM_SALARY) FROM JOBS) * 1.08;
cont> -- Update the rows in the JOBS table.
cont> UPDATE JOBS
cont> SET MINIMUM_SALARY = :MIN_SAL
cont> WHERE MINIMUM_SALARY < (:MIN_SAL * 1.08);
cont> END;
Example 2: Using the DEFAULT clause
The following example shows several variable declarations using a
variety of value expressions for the DEFAULT clause.
SQL> SET FLAGS 'TRACE';
SQL>
SQL> BEGIN
cont> DECLARE :x INTEGER DEFAULT -1;
cont> TRACE :x;
cont> END;
~Xt: -1
SQL>
SQL> BEGIN
cont> DECLARE :x INTEGER DEFAULT NULL;
cont> TRACE COALESCE (:x, 'NULL');
cont> END;
~Xt: NULL
SQL>
SQL> BEGIN
cont> DECLARE :x INTEGER DEFAULT (1+1);
cont> TRACE :x;
cont> END;
~Xt: 2
SQL>
SQL> BEGIN
cont> DECLARE :x INTEGER DEFAULT (SELECT COUNT(*) FROM EMPLOYEES);
cont> TRACE :x;
cont> END;
~Xt: 100
Example 3: Specifying a LOOP statement using the DEFAULT clause
The following example shows some simple value expressions. The
default value is applied to :y on each iteration of the loop, not
just the first time the statement is executed.
SQL> BEGIN
cont> DECLARE :x INTEGER DEFAULT 0;
cont> WHILE :x < 10
cont> LOOP
cont> BEGIN
cont> DECLARE :y INTEGER DEFAULT 1;
cont> TRACE :x, :y;
cont> SET :x = :x + :y;
cont> SET :y = :y + 1;
cont> END;
cont> END LOOP;
cont> END;
~Xt: 0 1
~Xt: 1 1
~Xt: 2 1
~Xt: 3 1
~Xt: 4 1
~Xt: 5 1
~Xt: 6 1
~Xt: 7 1
~Xt: 8 1
~Xt: 9 1
Example 4: Using the CHECK constraint
This example shows the use of a CHECK constraint to prevent
illegal values being assigned to control variables for a REPEAT
loop. The singleton SELECT will actually return zero to the local
variable P which will cause a variable validation to fail.
SQL> begin
cont> declare :v integer = 0 check (value is not null);
cont> declare :p integer = 1 check (value is not null and value <> 0);
cont>
cont> repeat
cont> select count(*) into :p
cont> from employees
cont> where employee_id = '00000';
cont> set :v = :v + :p;
cont> until :v > 1000
cont> end repeat;
cont> end;
%RDB-E-NOT_VALID, validation on field P caused operation to fail
Example 5: Using the WITH HOLD clause
The following example shows the use of the WITH HOLD PRESERVE
ON COMMIT clause in a procedure which purges old data from the
AUDIT_HISTORY table. It commits the transaction every 100 rows
(:MAX_UNIT).
SQL> declare transaction read only;
SQL> set flags 'TRACE';
SQL> set compound transactions 'internal';
SQL> declare :purge_date date;
SQL> accept :purge_date prompt 'Purge date for AUDIT_HISTORY? ';
Purge date for AUDIT_HISTORY? 1-jan-1989
SQL>
SQL> begin
cont> with hold preserve on commit
cont> declare :max_unit constant integer = 100;
cont> declare :rc integer = :max_unit;
cont>
cont> set transaction read write;
cont> for :ah
cont> as table cursor ah_cursor
cont> for select * from audit_history
cont> where job_start < :purge_date
cont> do
cont> delete from audit_history
cont> where current of ah_cursor;
cont> if :rc = 0
cont> then
cont> commit;
cont> set :rc = :max_unit;
cont> set transaction read write;
cont> else
cont> set :rc = :rc - 1;
cont> end if;
cont> end for;
cont> get diagnostics :rc = ROW_COUNT;
cont> commit;
cont> trace 'Processed rows: ', :rc;
cont> end;
~Xt: Processed rows: 1096