oracle中存储过程procedure的异常处理

create or replace procedure PROC_TO_T_PARAMETER is

  l_airBestCity varchar2(50);
  l_currentDay  date;
  l_air30daysI  varchar2(100);
  l_errorcode   varchar2(100);
  
begin
  -- author sunchengliang
  --当天时间
  select max(tt.oper_date) into l_currentDay from city_day tt;

  --当天空气质量最好的城市 
  select t.city
    into l_airBestCity
    from city_day t
   where t.oper_date = l_currentDay
     and t.pollution_indeces =
         (select min(ttt.pollution_indeces)
            from city_day ttt
           where ttt.oper_date = l_currentDay);

  --30内一级天数最多的城市  
     select tt.city
    into l_air30daysI
    from (select t.city, sum(decode(grade, ''Ⅰ'', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 30
             and t.oper_date  < l_currentDay
           group by t.city
order by total1 desc) tt
   where tt.total1 = (select max(sum(decode(grade, ''Ⅰ'', 1, 0))) total1
                        from city_day t
                       where t.oper_date > l_currentDay - 30
                         and t.oper_date  < l_currentDay
                       group by t.city) and rownum=1;
  --更新到db 
  update T_PARAMETER t
     set t.p_content = ''今天空气量最好的城市是:''  ¦ ¦ l_airBestCity  ¦ ¦ ''#''  ¦ ¦
                       ''最近30天内空气质量一级天数最多的城市:'' ¦ ¦l_air30daysI
   where t.p_type = ''1'';
  Commit;
exception
  When Others Then
    l_errorcode := sqlerrm;
    Rollback;
  
end PROC_TO_T_PARAMETER;

请注意格式



create or replace procedure PROC_TO_T_PARAMETER is
--your temporary variables


begin
--your sql here
  ...
  commit;
excption
     When Others Then
     l_errorcode := sqlerrm;
     Rollback;
end;

当sql语句出现异常时,就会跳到exception代码段,sqlerrm会显示出错误信息.也可以把它赋给out参数,返回给用户。并回滚。如果sql没有异常，commit，完成操作．