1  LEAVE_Control
   Unconditionally ends execution within a compound statement block
   or a looping statement but resumes execution on any SQL statement
   that immediately follows the exited statement.
 

2  Environment
   You can use the LEAVE control statement in a compound statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 leave-statement =
 
 ---> LEAVE -+----------------------+-->
             +-> <statement-label> -+

 

2  Arguments
 

3  statement-label

   Names the label assinged to a compound statement, loop statement,
   or multistatement procedure.
 

2  Examples
   Example 1: Using the LEAVE control statement within a loop

   SQL> set flags 'trace';
   SQL>
   SQL> create module REPORTS
   cont> /*
   ***> This procedure counts the employees of a given state
   ***> who have had a decrease in their salary during their
   ***> employment
   ***> */
   cont> procedure COUNT_DECREASED
   cont>     (in :state CHAR(2)
   cont>     ,inout :n_decreased INTEGER);
   cont> begin
   cont> set :n_decreased = 0;
   cont>
   cont> EMP_LOOP:
   cont> for :empfor
   cont>     as each row of
   cont>         select employee_id
   cont>          from EMPLOYEES where state = :state
   cont> do
   cont>     begin
   cont>     declare :last_salary INTEGER (2) default 0;
   cont>
   cont>     HISTORY_LOOP:
   cont>     for :salfor
   cont>         as each row of
   cont>             select salary_amount
   cont>              from SALARY_HISTORY
   cont>              where employee_id = :empfor.employee_id
   cont>              order by salary_start
   cont>     do
   cont>         if :salfor.salary_amount < :last_salary
   cont>         then
   cont>             set :n_decreased = :n_decreased + 1;
   cont>             trace :empfor.employee_id, ': ', :salfor.salary_amount;
   cont>             leave HISTORY_LOOP;
   cont>         end if;
   cont>
   cont>         set :last_salary = :salfor.salary_amount;
   cont>     end for;
   cont>     end;
   cont> end for;
   cont> end;
   cont>
   cont> end module;
   SQL>
   SQL> declare :n integer;
   SQL> call COUNT_DECREASED ('NH', :n);
   ~Xt: 00200: 40789.00
   ~Xt: 00248: 46000.00
   ~Xt: 00471: 52000.00
              N
              3
   SQL>
   SQL> rollback;

   Example 2: Ending Execution of a Compound Statement

   PROCEDURE SAMPLE (IN :ID MONEY);
   BEGIN
   DECLARE: AMOUNT MONEY
            (SELECT TOTAL_AMOUNT FROM M_TABLE);
   LOOP
      IF :AMOUNT IS NULL THEN
         LEAVE;
      END IF;
      .
      .
      .
      SET :AMOUNT =:AMOUNT-100.00;
      IF :AMOUNT < 0.00 THEN
          LEAVE;
      END IF;
   END LOOP;
   END;