Oracle 如何使用循环控制关键字exit、goto、continue

 

---

目录

1. exit 使用

2. goto使用

3. continue使用

4. oracle不支持break

---

1. exit 使用

---

exit用于跳出循环， 在10g和11g都可以正常使用

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      if (i = 5) then
  4        exit;
  5      end if;
  6      dbms_output.put_line(i);
  7    end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      if (i = 5) then
  4        exit;
  5      end if;
  6      dbms_output.put_line(i);
  7    end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

---

2. goto使用

---

goto可以在10g和11g使用，有的人说11g不能使用经过验证此说法是错误的， goto使用方法如下

1. <<next_step>> 是循环标签，名称可以自定义，比如示例中也可以替换为<<next>>
2. 当使用goto next_step的时候，程序会跳转到定义的 <<next_step>>标签
3. <<next_step>> 后面不能跟end loop 或者exception关键字，所以使用NULL隔开

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> begin
  2  
  3    for i in 1 .. 20 loop
  4      if (i > 5) then
  5        goto next_step;
  6      end if;
  7      dbms_output.put_line(i);
  8  
  9      <<next_step>>  --goto跳转到这里进入下次循环
 10      NULL;
 11    end loop;
 12  
 13  end;
 14  /
 
PL/SQL procedure successfully completed

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      if (i > 5) then
  4        goto next_step;
  5      end if;
  6      dbms_output.put_line(i);
  7  
  8      <<next_step>>  --goto跳转到这里进入下次循环
  9      NULL;
 10    end loop;
 11  end;
 12  /
 
PL/SQL procedure successfully completed

---

3. continue使用

---

 continue用于继续下次循环, continue关键字是11g增加，因此在Oracle 10g中使用的时候会提示 没有声明CONTINUE

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      -- 判断
  4      if (i = 5) then
  5        continue;
  6      end if;
  7      dbms_output.put_line(i);
  8    end loop;
  9  end;
 10  /
 
 
ORA-06550: line 6, column 7:
PLS-00201: identifier 'CONTINUE' must be declared
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored

 continue关键字在Oracle 11g中可以使用

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      -- 判断
  4      if (i = 5) then
  5        continue;
  6      end if;
  7      dbms_output.put_line(i);
  8    end loop;
  9  end;
 10  /
 
PL/SQL procedure successfully completed

---

4. oracle不支持break

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      -- 判断
  4      if (i = 5) then
  5        break;
  6      end if;
  7      dbms_output.put_line(i);
  8    end loop;
  9  end;
 10  /
 

ORA-06550: line 6, column 7:
PLS-00201: identifier 'BREAK' must be declared
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> begin
  2    for i in 1 .. 20 loop
  3      -- 判断
  4      if (i = 5) then
  5        break;
  6      end if;
  7      dbms_output.put_line(i);
  8    end loop;
  9  end;
 10  /
 
ORA-06550: 第 6 行, 第 7 列: 
PLS-00201: identifier 'BREAK' must be declared
ORA-06550: 第 6 行, 第 7 列: 
PL/SQL: Statement ignored

上一篇：Oracle 10g如何修改用户名称