循环语句

-- Start

LOOP

DECLARE
  AMOUNT INT := 0;

BEGIN
  LOOP
    AMOUNT := AMOUNT + 1;
    
    IF AMOUNT > 4 THEN
      EXIT; -- 退出循环
    END IF;
    
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(AMOUNT);
END;
/

循环控制语句

DECLARE
  AMOUNT INT := 0;

BEGIN
  LOOP
    AMOUNT := AMOUNT + 1;
    
    -- CONTINUE 执行下一循环
    IF AMOUNT = 3 THEN   
      CONTINUE;  
    END IF;
    
    -- CONTINUE WHEN 执行下一循环
    CONTINUE WHEN AMOUNT = 4;
    
    -- EXIT 退出循环
    IF AMOUNT = 10 THEN   
      EXIT;  
    END IF;
    
    -- EXIT WHEN 退出循环
    EXIT WHEN AMOUNT = 10;
  END LOOP;
END;
/

FOR

DECLARE  
  AMOUNT INT := 0;  
  
BEGIN
  -- 从小到大循环：1+2+3
  FOR i IN 1..3 LOOP
    AMOUNT := AMOUNT + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (AMOUNT);
  
  -- 从大到小循环：3+2+1
  AMOUNT := 0;
  FOR i IN REVERSE 1..3 LOOP
    AMOUNT := AMOUNT + i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (AMOUNT);
END;
/

我们还可以通过 FOR 语句迭代表。

CREATE TABLE Student(
    Id      INT             NOT NULL,
    Name    VARCHAR2(30)    NOT NULL,
    Class   VARCHAR2(10)
);
INSERT INTO Student values (1, '张三', '116班');
INSERT INTO Student values (2, '李四', '117班');

BEGIN
  FOR StudentVar IN (
    SELECT * FROM Student
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('学号: ' || StudentVar.Id || ', 姓名: ' || StudentVar.Name );
  END LOOP;
END;
/

WHILE

DECLARE
  AMOUNT INT := 0;

BEGIN
  WHILE AMOUNT < 4 LOOP
    AMOUNT := AMOUNT + 1;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(AMOUNT);
END;
/

标签，GOTO，NULL

BEGIN
  -- 可以给循环语句定义标签
  <<FIRST_FOR>>
  WHILE TRUE LOOP
  
    <<SECOND_FOR>>
    FOR i IN 1..9 LOOP
      IF i = 9 THEN
        CONTINUE FIRST_FOR; -- 跳转到 WHILE 循环
      END IF;
      
      IF i = 8 THEN
        GOTO FIRST_FOR; -- 跳转到 WHILE 循环
      ELSE
        NULL; -- NULL 语句表示什么也不干
      END IF;
      
      EXIT FIRST_FOR WHEN i = 5;
    END LOOP;
  END LOOP;

END;
/

--更多参见：Oracle PL/SQL 精萃

-- 声明：转载请注明出处

-- Last Edited on 2015-01-12

-- Created by ShangBo on 2015-01-02

-- End