Oracle存储过程，在一个for循环里面，要catch到异常，然后continue或者跳出循环怎么做

[keanu196492]

代码不方便发，实例如下：

for i in 1..len Loop
select * from test where id=i; -- 查不到就抛no_data_found

exception when no_data_found then -- 遇到异常做一些处理
begin
continue; --继续Loop 或者exit; --跳出循环
end;
end Loop;


这样根本是编译不过的，把exception when 从 for里面去掉就行了，或者是exception写在最外面
总之就是不能写在for里面，这样的话，这种逻辑用存储过程不是完成不了么，我觉得这种情况还是挺常见的，在java里面try catch 然后continue或者break就行了。这里该怎么做呢？请指教

[ngx20080110]

declare
  v_name varchar2(50);
begin
  for i in 100..300 loop
    begin
      select first_name || '.' || last_name into v_name from employees where employee_id = i;
      dbms_output.put_line('#' || i || ': ' || v_name);
    exception
      when no_data_found then
        dbms_output.put_line('Not find #' || i);
      when others then
        exit;    
    end;
  end loop;
end;

[minitoy]

SQL> set serveroutput on

SQL> create or replace procedure proc_test_continue
  2  as
  3  v_num number(10);
  4  begin
  5  for i in 1..10 Loop
  6    begin
  7    select a into v_num from t_test where a=i; -- 查不到就抛no_data_found
  8    dbms_output.put_line('we get it');
  9    exception when no_data_found then -- 遇到异常做一些处理
 10       dbms_output.put_line('something wrong');
 11    end;
 12  
 13  end Loop;
 14  end;
 15  /
 
Procedure created
 
SQL> exec proc_test_continue
 
we get it
something wrong
something wrong
something wrong
something wrong
something wrong
something wrong
something wrong
something wrong
something wrong
 
PL/SQL procedure successfully completed
 
SQL> 

[minitoy]

块的基本形式
declare
begin
exception
end

declare里面的变量仅在本块及其子块有意义.类似c里面函数的局部变量.

[ojuju10]

10g不支持continue,但是有exit

11g才支持continue

[keanu196492]

多谢LS的2位，我去试试，OK的话，分都给你们

[minitoy]

SQL> create or replace procedure proc_test_continue
  2  as
  3  v_num number(10);
  4  begin
  5  for i in 1..10 Loop
  6    declare
  7    v_c varchar2(10);
  8    begin
  9    v_c:='123';
 10    select a into v_num from t_test where a=i; -- 查不到就抛no_data_found
 11    dbms_output.put_line('we get it');
 12    exception when no_data_found then -- 遇到异常做一些处理
 13       dbms_output.put_line('something wrong');
 14       exit;
 15    end;
 16  end Loop;
 17  dbms_output.put_line('exit successful');
 18  end;
 19  /
 
Procedure created
 
SQL> exec proc_test_continue
 
we get it
something wrong
exit successful
 
PL/SQL procedure successfully completed
 
SQL> 

[keanu196492]

[Quote=引用 3 楼 minitoy 的回复:]
块的基本形式
declare
begin
exception
end

declare里面的变量仅在本块及其子块有意义.类似c里面函数的局部变量.
[/Quote]

了解了。