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