PL/SQL中的循环控制语句

Posted by 道行尚浅 on January 22, 2008

循环是一种编程机制,允许一组指令被反复执行. 在PL/SQL中使用循环的机会并没有其他语言那么多.

简单循环

[ label ] LOOP
  statements
END LOOP [ label ];

[ label ] 是循环中的标签,可以用来标识一个循环.

在嵌套循环中有着广泛的应用.

LOOP 关键字标识循环开始

END LOOP 标识循环结束.

下面的例程是一个死循环,因为我们没有指定循环的退出条件

begin
  loop
    null ;
  end loop ;
end ;

循环的退出

可以在循环中使用EXIT 语句退出循环

EXIT 语句有两种形式:

  • IF EXIT ;

例程:打印1-5

DECLARE
  V_COUNTER NUMBER := 0;
BEGIN
  LOOP
    V_COUNTER := V_COUNTER + 1;
    DBMS_OUTPUT.put_line(V_COUNTER);
    IF V_COUNTER = 5 THEN
      EXIT;
    END IF;
  END LOOP;
END;

执行结果

1
2
3
4
5

PL/SQL procedure successfully completed

  • EXIT WHEN ;

例程:打印1-5

DECLARE
V_COUNTER NUMBER :=0 ;
BEGIN
 LOOP
   V_COUNTER :=V_COUNTER+1 ;
   DBMS_OUTPUT.put_line(V_COUNTER);
   EXIT WHEN V_COUNTER = 5 ;
 END LOOP ;
END ;  
  

执行结果

1
2
3
4
5

PL/SQL procedure successfully completed

WHILE 循环

WHILE 关键字后是循环可以运行需满足的条件 与简单循环不同,WHILE循环是先判断后执行

例程:打印1-5

[ label ] WHILE condition LOOP
  statements
END LOOP [ label ];
DECLARE
  V_COUNTER NUMBER := 0;
BEGIN
  WHILE V_COUNTER <5 
  LOOP
    V_COUNTER := V_COUNTER + 1;
    DBMS_OUTPUT.put_line(V_COUNTER);
  END LOOP;
END;

执行结构

1
2
3
4
5

PL/SQL procedure successfully completed

FOR 循环

FOR 循环可以生成一个数列,数列从lower_bound开始到upper_bound

FOR 关键字后是一个数值型变量,这个变量不需要提前声明,也不能被赋值.

REVERSE 关键字可以使数列降序输出

[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ label ];

例程:打印1-5

BEGIN
  FOR I IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line(I);
  END LOOP;
END;

执行结果:

1
2
3
4
5

PL/SQL procedure successfully completed

例程:打印5-1

5
4
3
2
1

PL/SQL procedure successfully completed

BEGIN
  FOR I IN  1 .. 5  LOOP
    I :=10;  --对for循环自带的变量赋值,引发报错
    DBMS_OUTPUT.put_line(I);
  END LOOP;
END;

执行结果:

 ORA-06550: line 3, column 5:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

CONTNUTE 语句

CONTNUTE 语句可以提前结束本轮循环,开启下一轮循环. CONTNUTE 语句也有两种实现方式

  • IF CONTINUE 例程:打印1-10之间的偶数

BEGIN
  FOR I IN 1 .. 10 LOOP
    IF MOD(I,2)=1  THEN  --当I的值对2取余等于1的时候,执行CONTINUE语句,提前结束循环,跳过打印语句
      CONTINUE ;   
    END IF ;
    DBMS_OUTPUT.put_line(I);
  END LOOP;
END;

执行结果:

2
4
6
8
10

PL/SQL procedure successfully completed

  • CONTINUE WHEN

例程:打印1-10之间的偶数


BEGIN
  FOR I IN 1 .. 10 LOOP
    CONTINUE WHEN MOD(I,2)=1 ; 
    DBMS_OUTPUT.put_line(I);
  END LOOP;
END;

执行结果:

2
4
6
8
10

PL/SQL procedure successfully completed

嵌套循环

在循环内加入嵌入另一个循环语句,形成嵌套循环.

简单循环,WHILE循环,FOR循环可以互相嵌套.

非相关嵌套循环

如果内部循环循环的次数与外部循环计数器的值没有直接关系,这种循环我们称之为非相关嵌套循环


BEGIN
  <<outer_loop>>
  FOR i IN 1 .. 2 LOOP
    <<inner_loop>>
    FOR j IN 1 .. 5 LOOP
      DBMS_OUTPUT.PUT_LINE('outer i='||i||' j = ' || j);
    END LOOP inner_loop;
  END LOOP outer_loop;
END;

执行结果:

outer i=1 j = 1
outer i=1 j = 2
outer i=1 j = 3
outer i=1 j = 4
outer i=1 j = 5
outer i=2 j = 1
outer i=2 j = 2
outer i=2 j = 3
outer i=2 j = 4
outer i=2 j = 5

PL/SQL procedure successfully completed

相关嵌套循环

如果内部循环循环的次数与外部循环计数器的值存在直接关系,这种循环我们称之为相关嵌套循环

例程:九九乘法表


BEGIN
  FOR outer_cnt IN  1 .. 9  LOOP
     for inner_cnt in 1..outer_cnt  --内层循环的值受外层循环的值影响
     loop
        dbms_output.put(inner_cnt||'*'||outer_cnt||' = '||rpad(inner_cnt*outer_cnt,3,' '));
     end loop ;
     dbms_output.put_line(' ');
  END LOOP;
END;

执行结果:

1*1 = 1   
1*2 = 2  2*2 = 4   
1*3 = 3  2*3 = 6  3*3 = 9   
1*4 = 4  2*4 = 8  3*4 = 12 4*4 = 16  
1*5 = 5  2*5 = 10 3*5 = 15 4*5 = 20 5*5 = 25  
1*6 = 6  2*6 = 12 3*6 = 18 4*6 = 24 5*6 = 30 6*6 = 36  
1*7 = 7  2*7 = 14 3*7 = 21 4*7 = 28 5*7 = 35 6*7 = 42 7*7 = 49  
1*8 = 8  2*8 = 16 3*8 = 24 4*8 = 32 5*8 = 40 6*8 = 48 7*8 = 56 8*8 = 64  
1*9 = 9  2*9 = 18 3*9 = 27 4*9 = 36 5*9 = 45 6*9 = 54 7*9 = 63 8*9 = 72 9*9 = 81  

PL/SQL procedure successfully completed