02-loops.sql

《Oracle8i PL/SQL程序设计》附源码

REM 02-LOOPS.SQL
REM This file contains the loop examples used in Chapter 2 of
REM "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/18/96.
REM Comments and questions should go to Scott Urman at
REM [e-mail].


REM *** Chapter 2: Simple Loop ***
DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- Insert a row into temp_table with the current value of the
    -- loop counter.
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will 
    -- break out of the loop.
    IF v_Counter > 50 THEN
      EXIT;
    END IF;
  END LOOP;
END;
/

REM *** Chapter 2: Using EXIT WHEN ***
 DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- Insert a row into temp_table with the current value of the
    -- loop counter.
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will 
    -- break out of the loop.
    EXIT WHEN v_Counter > 50;
  END LOOP;
END;
/

REM *** Chapter 2: WHILE Loop Example 1 ***
DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  -- Test the loop counter before each loop iteration to
  -- insure that it is still less than 50.
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;
/

REM *** Chapter 2: WHILE Loop Example 2 ***
DECLARE
  v_Counter BINARY_INTEGER;
BEGIN
  -- This condition will evaluate to NULL, since v_Counter
  -- is initialized to NULL by default.
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;
/

REM *** Chapter 2: Numeric FOR Loop ***
BEGIN
  FOR v_Counter IN 1..50 LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop Index');
  END LOOP;
END;
/

REM *** Chapter 2: FOR Loop Scoping Rules ***
DECLARE
  v_Counter  NUMBER := 7;
BEGIN
  -- Inserts the value 7 into temp_table.
  INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
  -- This loop redeclares v_Counter as a BINARY_INTEGER, which hides
  -- the NUMBER declaration of v_Counter.
  FOR v_Counter IN 20..30 LOOP
    -- Inside the loop, v_Counter ranges from 20 to 30.
    INSERT INTO temp_table (num_col)
      VALUES (v_Counter);
  END LOOP;
  -- Inserts another 7 into temp_table.
  INSERT INTO temp_table (num_col)
    VALUES (v_Counter);
END;
/

REM *** Chapter 2: FOR Loop Ranges ***
DECLARE
  v_LowValue  NUMBER := 10;
  v_HighValue NUMBER := 40;
BEGIN
  FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Dynamically specified loop ranges');
  END LOOP;
END;
/

REM *** Chapter 2: Using Labels ***
DECLARE
  v_Counter  BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table
      VALUES (v_Counter, 'Loop count');
    v_Counter := v_Counter + 1;
    IF v_Counter > 50 THEN
      GOTO l_EndOfLoop;
    END IF;
  END LOOP;

  <<l_EndOfLoop>>
  INSERT INTO temp_table (char_col)
    VALUES ('Done!');
END;
/