Oracle LOOP循環(huán)控制語句

在PL/SQL中可以使用LOOP語句對數(shù)據(jù)進行循環(huán)處理，利用該語句可以循環(huán)執(zhí)行指定的語句序列。常用的LOOP循環(huán)語句包含3種形式：基本的LOOP、WHILE...LOOP和FOR...LOOP。

LOOP語句的基本語法結(jié)構(gòu)如下：
    [<<label_name>>]

LOOP
statement...
END LOOP [label_name]

【語法說明】
<<label_name>>：LOOP結(jié)構(gòu)的標簽，是可選項。
LOOP：LOOP循環(huán)開始標志。
statement：LOOP語句中循環(huán)執(zhí)行的語句序列。
END LOOP：LOOP循環(huán)結(jié)束標志，可以添加LOOP結(jié)構(gòu)的標簽。
1.基本的LOOP語句
實例：要求聲明變量，每次循環(huán)都需要為變量增加數(shù)字1，并輸出結(jié)果。當變量值大于100時，退出循環(huán)操作。

a、利用EXIT...WHEN結(jié)束循環(huán)操作。

SQL> declare
2 v_rlt number(3):=1;
3 begin
4 <<fst_loop>>
5 loop
6 dbms_output.put_line('v_rlt = '||v_rlt);
7 v_rlt:=v_rlt+1;
8 exit fst_loop when v_rlt > 100;
9 end loop;
10 dbms_output.put_line('LOOP循環(huán)已經(jīng)結(jié)束！');
11 end;
12 /
v_rlt = 1
v_rlt = 2
v_rlt = 3
v_rlt = 4
v_rlt = 5
v_rlt = 6
v_rlt = 7
v_rlt = 8
v_rlt = 9
v_rlt = 10
v_rlt = 11
v_rlt = 12
v_rlt = 13
v_rlt = 14
v_rlt = 15
v_rlt = 16
v_rlt = 17
v_rlt = 18
v_rlt = 19
v_rlt = 20
v_rlt = 21
v_rlt = 22
v_rlt = 23
v_rlt = 24
v_rlt = 25
v_rlt = 26
v_rlt = 27
v_rlt = 28
v_rlt = 29
v_rlt = 30
v_rlt = 31
v_rlt = 32
v_rlt = 33
v_rlt = 34
v_rlt = 35
v_rlt = 36
v_rlt = 37
v_rlt = 38
v_rlt = 39
v_rlt = 40
v_rlt = 41
v_rlt = 42
v_rlt = 43
v_rlt = 44
v_rlt = 45
v_rlt = 46
v_rlt = 47
v_rlt = 48
v_rlt = 49
v_rlt = 50
v_rlt = 51
v_rlt = 52
v_rlt = 53
v_rlt = 54
v_rlt = 55
v_rlt = 56
v_rlt = 57
v_rlt = 58
v_rlt = 59
v_rlt = 60
v_rlt = 61
v_rlt = 62
v_rlt = 63
v_rlt = 64
v_rlt = 65
v_rlt = 66
v_rlt = 67
v_rlt = 68
v_rlt = 69
v_rlt = 70
v_rlt = 71
v_rlt = 72
v_rlt = 73
v_rlt = 74
v_rlt = 75
v_rlt = 76
v_rlt = 77
v_rlt = 78
v_rlt = 79
v_rlt = 80
v_rlt = 81
v_rlt = 82
v_rlt = 83
v_rlt = 84
v_rlt = 85
v_rlt = 86
v_rlt = 87
v_rlt = 88
v_rlt = 89
v_rlt = 90
v_rlt = 91
v_rlt = 92
v_rlt = 93
v_rlt = 94
v_rlt = 95
v_rlt = 96
v_rlt = 97
v_rlt = 98
v_rlt = 99
v_rlt = 100
LOOP循環(huán)已經(jīng)結(jié)束！

PL/SQL procedure successfully completed.
b、利用IF...EXIT語句結(jié)束循環(huán)
SQL> declare
2 v_rlt number(3):=1;
3 begin
4 <<fst_loop>>
5 loop
6 dbms_output.put_line('v_rlt = '||v_rlt);
7 v_rlt:=v_rlt+1;
8 if v_rlt > 100 then
9 dbms_output.put_line('變量的值已經(jīng)大于100，當前值為'||v_rlt);
10 exit fst_loop;
11 end if;
12 end loop;
13 dbms_output.put_line('LOOP循環(huán)已經(jīng)結(jié)束！');
14 end;
15 /
v_rlt = 1
v_rlt = 2
v_rlt = 3
v_rlt = 4
v_rlt = 5
v_rlt = 6
v_rlt = 7
v_rlt = 8
v_rlt = 9
v_rlt = 10
v_rlt = 11
v_rlt = 12
v_rlt = 13
v_rlt = 14
v_rlt = 15
v_rlt = 16
v_rlt = 17
v_rlt = 18
v_rlt = 19
v_rlt = 20
v_rlt = 21
v_rlt = 22
v_rlt = 23
v_rlt = 24
v_rlt = 25
v_rlt = 26
v_rlt = 27
v_rlt = 28
v_rlt = 29
v_rlt = 30
v_rlt = 31
v_rlt = 32
v_rlt = 33
v_rlt = 34
v_rlt = 35
v_rlt = 36
v_rlt = 37
v_rlt = 38
v_rlt = 39
v_rlt = 40
v_rlt = 41
v_rlt = 42
v_rlt = 43
v_rlt = 44
v_rlt = 45
v_rlt = 46
v_rlt = 47
v_rlt = 48
v_rlt = 49
v_rlt = 50
v_rlt = 51
v_rlt = 52
v_rlt = 53
v_rlt = 54
v_rlt = 55
v_rlt = 56
v_rlt = 57
v_rlt = 58
v_rlt = 59
v_rlt = 60
v_rlt = 61
v_rlt = 62
v_rlt = 63
v_rlt = 64
v_rlt = 65
v_rlt = 66
v_rlt = 67
v_rlt = 68
v_rlt = 69
v_rlt = 70
v_rlt = 71
v_rlt = 72
v_rlt = 73
v_rlt = 74
v_rlt = 75
v_rlt = 76
v_rlt = 77
v_rlt = 78
v_rlt = 79
v_rlt = 80
v_rlt = 81
v_rlt = 82
v_rlt = 83
v_rlt = 84
v_rlt = 85
v_rlt = 86
v_rlt = 87
v_rlt = 88
v_rlt = 89
v_rlt = 90
v_rlt = 91
v_rlt = 92
v_rlt = 93
v_rlt = 94
v_rlt = 95
v_rlt = 96
v_rlt = 97
v_rlt = 98
v_rlt = 99
v_rlt = 100
變量的值已經(jīng)大于100，當前值為101
LOOP循環(huán)已經(jīng)結(jié)束！

PL/SQL procedure successfully completed.
2、WHILE...LOOP結(jié)構(gòu)
WHILE...LOOP結(jié)構(gòu)和基本的LOOP語句不同，它本身可以結(jié)束LOOP循環(huán)。WHILE關(guān)鍵詞后面需要有布爾表達式，當WHILE后面的布爾表達式為TRUE時，則循環(huán)體重的語句序列被執(zhí)行1次，然后會重新判斷WHILE后面的表達式是否為TRUE，只有當WHILE后的布爾表達式為FALSE時，才結(jié)束整個LOOP循環(huán)。

該語句結(jié)構(gòu)的相關(guān)語法如下：
    [<<label_name>>]

WHILE boolean_expression
LOOP
statement...
END LOOP [label_name];
【語法說明】
boolean_expression：布爾表達式。
statement：語句序列，當boolean_expression為TRUE時，該語句序列可獲得執(zhí)行權(quán)。
SQL> declare
2 v_rlt number(3):=1;
3 begin
4 <<while_loop>>
5 while( v_rlt < 100)
6 loop
7 dbms_output.put_line('v_rlt = '||v_rlt);
8 v_rlt:=v_rlt+1;
9 end loop;
10 dbms_output.put_line('while循環(huán)已經(jīng)結(jié)束！');
11 end;
12 /
v_rlt = 1
v_rlt = 2
v_rlt = 3
v_rlt = 4
v_rlt = 5
v_rlt = 6
v_rlt = 7
v_rlt = 8
v_rlt = 9
v_rlt = 10
v_rlt = 11
v_rlt = 12
v_rlt = 13
v_rlt = 14
v_rlt = 15
v_rlt = 16
v_rlt = 17
v_rlt = 18
v_rlt = 19
v_rlt = 20
v_rlt = 21
v_rlt = 22
v_rlt = 23
v_rlt = 24
v_rlt = 25
v_rlt = 26
v_rlt = 27
v_rlt = 28
v_rlt = 29
v_rlt = 30
v_rlt = 31
v_rlt = 32
v_rlt = 33
v_rlt = 34
v_rlt = 35
v_rlt = 36
v_rlt = 37
v_rlt = 38
v_rlt = 39
v_rlt = 40
v_rlt = 41
v_rlt = 42
v_rlt = 43
v_rlt = 44
v_rlt = 45
v_rlt = 46
v_rlt = 47
v_rlt = 48
v_rlt = 49
v_rlt = 50
v_rlt = 51
v_rlt = 52
v_rlt = 53
v_rlt = 54
v_rlt = 55
v_rlt = 56
v_rlt = 57
v_rlt = 58
v_rlt = 59
v_rlt = 60
v_rlt = 61
v_rlt = 62
v_rlt = 63
v_rlt = 64
v_rlt = 65
v_rlt = 66
v_rlt = 67
v_rlt = 68
v_rlt = 69
v_rlt = 70
v_rlt = 71
v_rlt = 72
v_rlt = 73
v_rlt = 74
v_rlt = 75
v_rlt = 76
v_rlt = 77
v_rlt = 78
v_rlt = 79
v_rlt = 80
v_rlt = 81
v_rlt = 82
v_rlt = 83
v_rlt = 84
v_rlt = 85
v_rlt = 86
v_rlt = 87
v_rlt = 88
v_rlt = 89
v_rlt = 90
v_rlt = 91
v_rlt = 92
v_rlt = 93
v_rlt = 94
v_rlt = 95
v_rlt = 96
v_rlt = 97
v_rlt = 98
v_rlt = 99
while循環(huán)已經(jīng)結(jié)束！

PL/SQL procedure successfully completed.
3.FOR...LOOP結(jié)構(gòu)
FOR...LOOP語句可以遍歷某個范圍的整數(shù)，該范圍被FOR和LOOP關(guān)鍵詞封閉。首次進入循環(huán)時，循環(huán)范圍將被確定，并且以后不會再次計算。每循環(huán)一次，循環(huán)指數(shù)將會自動增加1。

FOR...LOOP語句的語法結(jié)構(gòu)如下
    [<<label_name>>]

FOR index_name IN
[ REVERSE ]
lower_bound .. upper_bound
LOOP
statement...
END LOOP [label_name];
【語法說明】
index_name：循環(huán)計數(shù)器，是一個變量，它可以得到當前的循環(huán)指數(shù)。需要注意的是，不能為其手工賦值。
REVERSE：可選項，指定循環(huán)方式。默認的循環(huán)方式由下標(lower_bound)到上標(upper_bound)。使用該選項則從上標界到下標界。
lower_bound：循環(huán)范圍的下標界。
upper_bound：循環(huán)范圍的上標界。
下標和上標之間的".."不能省略。
SQL> declare
2 v_rlt number(3):=1;
3 begin
4 for v_rlt in 1..100 loop
5 dbms_output.put_line('v_rlt = '||v_rlt);
6 end loop;
7 dbms_output.put_line('for循環(huán)已經(jīng)結(jié)束！');
8 end;
9 /
v_rlt = 1
v_rlt = 2
v_rlt = 3
v_rlt = 4
v_rlt = 5
v_rlt = 6
v_rlt = 7
v_rlt = 8
v_rlt = 9
v_rlt = 10
v_rlt = 11
v_rlt = 12
v_rlt = 13
v_rlt = 14
v_rlt = 15
v_rlt = 16
v_rlt = 17
v_rlt = 18
v_rlt = 19
v_rlt = 20
v_rlt = 21
v_rlt = 22
v_rlt = 23
v_rlt = 24
v_rlt = 25
v_rlt = 26
v_rlt = 27
v_rlt = 28
v_rlt = 29
v_rlt = 30
v_rlt = 31
v_rlt = 32
v_rlt = 33
v_rlt = 34
v_rlt = 35
v_rlt = 36
v_rlt = 37
v_rlt = 38
v_rlt = 39
v_rlt = 40
v_rlt = 41
v_rlt = 42
v_rlt = 43
v_rlt = 44
v_rlt = 45
v_rlt = 46
v_rlt = 47
v_rlt = 48
v_rlt = 49
v_rlt = 50
v_rlt = 51
v_rlt = 52
v_rlt = 53
v_rlt = 54
v_rlt = 55
v_rlt = 56
v_rlt = 57
v_rlt = 58
v_rlt = 59
v_rlt = 60
v_rlt = 61
v_rlt = 62
v_rlt = 63
v_rlt = 64
v_rlt = 65
v_rlt = 66
v_rlt = 67
v_rlt = 68
v_rlt = 69
v_rlt = 70
v_rlt = 71
v_rlt = 72
v_rlt = 73
v_rlt = 74
v_rlt = 75
v_rlt = 76
v_rlt = 77
v_rlt = 78
v_rlt = 79
v_rlt = 80
v_rlt = 81
v_rlt = 82
v_rlt = 83
v_rlt = 84
v_rlt = 85
v_rlt = 86
v_rlt = 87
v_rlt = 88
v_rlt = 89
v_rlt = 90
v_rlt = 91
v_rlt = 92
v_rlt = 93
v_rlt = 94
v_rlt = 95
v_rlt = 96
v_rlt = 97
v_rlt = 98
v_rlt = 99
v_rlt = 100
for循環(huán)已經(jīng)結(jié)束！

PL/SQL procedure successfully completed.