pl/sql cursor example

create or replace procedure PROC_TO_T_PARAMETER is



  l_airBestCity  varchar2(50);

  l_currentDay   date;

  l_air30daysI   varchar2(100);

  l_air30daysII  varchar2(100);

  l_air365daysI  varchar2(100);

  l_air365daysII varchar2(100);

  l_errorcode    varchar2(100);



begin

  -- author sunchengliang

  --当天时间

  select max(tt.oper_date) into l_currentDay from city_day tt;



  --当天空气质量最好的城市

  declare

    cursor c1 is

  select t.city city

    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);

  begin

    for r_c1 in c1 loop

      l_airBestCity := l_airBestCity||r_c1.city||' ';

    end loop;

  end;



  --最近30天内一级天数最多的城市

  declare

    cursor c2 is

    select tt.city city

      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);

  begin

    for r_c2 in c2 loop

      l_air30daysI := l_air30daysI||r_c2.city||' ';

    end loop;

  end;

      

  --最近30天内二级天数最多的城市

  declare 

    cursor c3 is

    select tt.city city

      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);

  begin

    for r_c3 in c3 loop

      l_air30daysII := l_air30daysII||r_c3.city||' ';

    end loop;

  end;                        

  --最近1年内一级天数最多的城市

  declare 

    cursor c4 is

      select tt.city city

        from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1

                from city_day t

               where t.oper_date > ADD_MONTHS(l_currentDay,-12)

                 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 > ADD_MONTHS(l_currentDay,-12)

                             and t.oper_date <= l_currentDay

                           group by t.city);

  begin

    for r_c4 in c4 loop

      l_air365daysI:= l_air365daysI||r_c4.city||' ';

    end loop;

  end;          

  --最近1年内二级天数最多的城市

  declare 

    cursor c5 is

      select tt.city city

        from (select t.city, sum(decode(grade, 'Ⅱ', 1, 0)) total1

                from city_day t

               where t.oper_date > ADD_MONTHS(l_currentDay,-12)

                 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 > ADD_MONTHS(l_currentDay,-12)

                             and t.oper_date <= l_currentDay

                           group by t.city);

  begin

    for r_c5 in c5 loop

        l_air365daysII := l_air365daysII||r_c5.city||' ';

    end loop;

  end;

  --更新到db

  update T_PARAMETER t

     set t.p_content = '今天空气质量最好的城市是:' || l_airBestCity || '#' ||

                       '最近30天内空气质量一级天数最多的城市:' || l_air30daysI || '#' ||

                       '二级天数最多的城市:' || l_air30daysII || '#' ||

                       '最近1年内空气质量一级天数最多的城市:' || l_air365daysI || '#' ||

                       '二级天数最多的城市:' || l_air365daysII

   where t.p_type = '1';

  Commit;

exception

  When Others Then

    l_errorcode := sqlerrm;

    begin

      insert into errormsg values('PROC_TO_T_PARAMETER',l_errorcode,sysdate);

      commit;

    end;

    Rollback;

  

end PROC_TO_T_PARAMETER;