Oracle procdure loop 循环报异常后如何继续下一次循环

本文介绍Oracle procdure loop的循环异常的处理方法：

     for num in 1..total loop

        begin

         strsql := 'select ssid from (select temp.sp_id as ssid, rownum as ssnum
                from tempInfo temp) where ssnum='|| num;
         execute immediate strsql into tempspid;

         strsql := 'update tempInfo set black_count =
                (select count(black.id) as blackcount
                from nm_black_and_object black where black.sp_id =' || tempspid || ')
                where sp_id=' || tempspid;
         execute immediate strsql;

        
         table_name := 'NM_NET_USER_'||tempspid;

        dbms_output.put_line(table_name);

         strsql := 'update tempInfo set white_count =
                (select count(distinct us.mdn) from NM_NET_USER_' || tempspid || ' us
                where us.status in (1,2,3))
                where sp_id=' || tempspid;
         execute immediate strsql;--此处可能引发 试图不存在异常 ，但是不该影响下面业务处理 

      end ;      
     end loop;

 

 

 

       --方法一 通过一次select 判断试图是否存在 ，如果存在则执行

         strsql := 'select count(*) from user_tables where table_name='''||table_name||'''';
         execute immediate strsql INTO counter ;

        

         IF counter > 0 THEN

               strsql := 'update tempInfo set white_count =
                (select count(distinct us.mdn) from NM_NET_USER_' || tempspid || ' us
                where us.status in (1,2,3))
                where sp_id=' || tempspid;更多Oracle文章请看http://www.cuug.com/
         execute immediate strsql;--此处可能引发 试图不存在异常 ，但是不该影响下面业务处理     

        
         END IF ;

 

    ---方法2 通过EXCEPTION

          strsql := 'update tempInfo set white_count =
                (select count(distinct us.mdn) from NM_NET_USER_' || tempspid || ' us
                where us.status in (1,2,3))
                where sp_id=' || tempspid;
         execute immediate strsql;--此处可能引发 试图不存在异常 ，但是不该影响下面的业务处理  

         exception

         when others then null ;  

关于Oracle procdure loop 循环报异常后如何继续下一次循环就介绍到这里，希望能给朋友们一些帮助或启迪。