1 Compound_Stmt 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. 2 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 --+--------------------+---+ +-> : -+ +-> pragma-clauses --+ | +------------------------------ <------------------------------+ +-+----------------------------+--+ ++--> variable-declaration -++ | +--------------------------+ | +---------------------------------+ +-+-------------------------------+--> END --+--------------------+--> ++-> compound-use-statement -++ +-> : -+ +---------- <-----------------+ pragma-clauses = --+-+-> pragma-option --+------------------+---> | +------- <---------+ | +-> PRAGMA ( -+-> pragma-option -+-> ) -+ +-------- , <-------+ pragma-option = --+-> ATOMIC ----------------------+--> +-> NOT ATOMIC ------------------+ +-> ON ALIAS -------+ +-> with-clause -----------------+ +-> optimize-clause -------------+ optimize-clause = --+---------------------------------------------------------------+---> +-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+ | | +-> TOTAL TIME --------+ | | | | +-> SEQUENTIAL ACCESS -+ | | | +-> USING ------------------+ | | +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ | | | +-> SAMPLED --+ | | | | +-> AGGRESSIVE + | | | +-> AS -----------------------+ | +---------------- <----------------------------+ with-clause = ---> WITH --> HOLD -+-------------------------------+-> +-> PRESERVE --+-> ON COMMIT ---+ +-> ON ROLLBACK -+ +-> ALL ---------+ +-> NONE --------+ variable-declaration = ---> DECLARE --+-> -+-+--------------+--+ +-------- , <--------+ +-> CONSTANT --+ | +-> UPDATABLE -+ | +-------------------------------------------------------+ +-+-> data-type ----+-+-------------------+-+----------------------+-> +-> + +-> default-clause -+ +-> constraint-clause -+ constraint-clause = --> CHECK --> (search-condition) -+--------------------------+-> +-> constraint-attributes -+ default-clause = --+-> DEFAULT --+--+--> date-time-literal -+-----> +-> = ------+ +--> interval-literal -+ +--> numeric-literal --+ +--> string-literal ---+ +--> : -+ 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 ------------+ 2 Arguments 3 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 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. 3 call-statement Invokes an external or stored procedure. See the CALL Compound_ Statement statement for a complete description. 3 case-searched-statement See the CASE_Searched help topic for a complete description. 3 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 compound-statement Lets you nest compound statements in another compound statement. 3 compound-use-statement Identifies the SQL statements allowed in a compound statement block. 3 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 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 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 delete-statement Deletes a row from a table or view. See the DELETE help topic for a complete description. 3 END Ends a compound statement block. 3 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 for-counted-loop-statement See the FOR (Counted) Control help topic for a complete description. 3 for-statement See the FOR_Control help topic for a complete description. 3 get-diagnostics-statement Retrieves diagnostic information for the previously executed statement. See the Get_Diagnostics help topic for a complete description. 3 if-statement See the IF_Control help topic for a complete description. 3 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 leave-statement See the LEAVE_Control help topic for a complete description. 3 lock-table-statement See the LOCK_TABLE help topic for a complete description. 3 loop-statement See the LOOP_Control help topic for a complete description. 3 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 OPTIMIZE_AS Assigns a name to the compound statement. 3 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 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 PRAGMA These options may only be specified on the outermost BEGIN statement. The exception is ATOMIC and NOT ATOMIC. 3 repeat-statement See the REPEAT_Control help topic for a complete description. 3 return-statement Returns the result for stored functions. See the RETURN_Control help topic for a complete description. 3 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 set-assignment-statement See the SET_Control help topic for a complete description. 3 set-transaction-statement Starts a transaction and specifies its characteristics. See the SET_TRANSACTION help topic for a complete description. 3 signal-statement See the SIGNAL_Control help topic for a complete description. 3 simple-case-statement See the CASE_Simple help topic for a complete description. 3 singleton-select-statement Specifies a one-row result table. See the SELECT Singleton_Select statement for a complete description. 3 start-transaction-statement See the START_TRANSACTION help topic for a complete description. 3 trace-statement Writes values to the trace log file. See the TRACE_Control help topic for a complete description. 3 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 update-statement Modifies a row in a table or view. See the UPDATE help topic for a complete description. 3 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 while-statement See the WHILE_Control help topic for a complete description. 3 WITH_HOLD Can be applied to a table cursor so that it remains open across COMMIT and ROLLBACK actions. 2 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