do until循环
会一直循环,直到满足exit条件就会退出循环
要中止循环,可以采用exit when语句或者exit if条件语句
当然,除0等异常可以中止循环
下为以上两种语句的语法结构
用if语句退出循环
loop
语句
if(表达式) then exit;
end if;
end loop;
---请看真正的示例
SQL> set serveroutput on
SQL> r
1 declare
2 counter integer:=10;
3 begin
4 loop
5 dbms_output.put_line(counter);
6 counter:=counter-1;
7 if(counter=0) then exit;
8 end if;
9 end loop;
10* end;
10
9
8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.
用exit when语句退出循环
loop
语句
exit when
end loop;
---请看如下示例
SQL> declare
2 counter integer:=10;
3 begin
4 loop
5 dbms_output.put_line(counter);
6 counter:=counter-1;
7 exit when counter=0;
8 end loop;
9 end;
10 /
10
9
8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.
SQL>
---采用exception退出do unitl 循环
SQL> declare
2 counter positive:=10; ---注意这里用的是positive而非integer
3 begin
4 loop
5 begin
6 dbms_output.put_line(counter);
7 counter:=counter-1;
8 exception when value_error then exit; --注意:语法为 exception when 异常名字 then 处理异常的动作;
9 end;
10 end loop;
11 end;
12 /
10
9
8
7
6
5
4
3
2
1
PL/SQL procedure successfully completed.
----while循环----
在执行循环之前先判断条件,可能判断条件后一次也进行循环
循环结构的语法
while(条件 is true) loop --注:这个条件不能为null,否则就成死循环了
语句
end loop;
示例如下:
SQL> r
1 declare
2 ascii_char integer:=97; ---打印小写字母序列
3 alphabet varchar2(26);
4 begin
5 while(nvl(length(alphabet),0)<26)
6 loop
7 alphabet:=alphabet||chr(ascii_char);
8 ascii_char:=ascii_char+1;
9 end loop;
10 dbms_output.put_line(alphabet); --注意:此处把dbms_output放在loop循环外面,就是仅打印alphabet最后的变量值
11* end;
abcdefghijklmnopqrstuvwxyz
PL/SQL procedure successfully completed.
---说明如何值为null的varchar类型与有值的varchar拼接,最终值为有值的varchar,而非null
SQL> declare
2 x varchar2(100);
3 y varchar2(10):='sex';
4 begin
5 x:=x||y;
6 dbms_output.put_line(x);
7 end;
8 /
sex
PL/SQL procedure successfully completed.
SQL>
---for 循环---
语法
for c in [reverse] a..b loop
语句
end loop;
注:for loop未必会一定循环,如当:a>b时,就不会循环
a=b,仅会循环一次
a
--示例如下:
SQL> declare ---未执行循环
2 str varchar2(10);
3 x integer:=11;
4 y integer:=10;
5 begin
6 for i in x..y loop
7 str:=str||'abc';
8 end loop;
9 dbms_output.put_line(nvl(str,'never execute for loop.'));
10 end;
11 /
never execute for loop.
PL/SQL procedure successfully completed.
SQL> r ---这个就仅执行1次循环
1 declare
2 str varchar2(10);
3 x integer:=10;
4 y integer:=10;
5 begin
6 for i in x..y loop
7 str:=str||'abc';
8 end loop;
9 dbms_output.put_line(nvl(str,'never execute for loop.'));
10* end;
abc
PL/SQL procedure successfully completed.
---do while do结构---
代码先执行一些任务,然后对条件进行判断。如果条件满足,则执行第2个任务
具体语法结构如下:
loop
语句
if(表达式) then exit;
end if;
语句
end loop;
loop
语句
exit when
语句
end loop;
具体示例如下:
SQL> declare
2 cursor c1 is select dname from test;
3 cursor_record c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into cursor_record;
8 exit when c1%notfound;
9 dbms_output.put_line(cursor_record.dname);
10 end loop;
11 end;
12 /
ACCOUNTING
RESEARCH
SALES
OPERATIONS
it2
it
PL/SQL procedure successfully completed.
SQL> r
1 declare
2 cursor c1 is select dname from test;
3 cursor_record c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into cursor_record;
8 if(c1%notfound) then exit;
9 end if;
10 dbms_output.put_line(cursor_record.dname);
11 end loop;
12* end;
ACCOUNTING
RESEARCH
SALES
OPERATIONS
it2
it
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-671199/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-671199/