oracle 存储过程示例

CREATE OR REPLACE PROCEDURE PROC_CITY_DATE2T_CITY_ORDERS IS
  l_maxOper_date date;
  l_maxId        number;
  l_errorcode   varchar2(100);

BEGIN

  -- author sunchengliang

  SELECT max(oper_date) into l_maxOper_date FROM city_day;
  select count(cityid) into l_maxId from t_city_orders;

  --插入
  INSERT INTO t_city_orders
    (oper_date,
     stat_type,
     cityid,
     city,
     excellent_days,
     fine_days,
     nogood_days,
     orders,
     l_excellent_days,
     l_fine_days,
     l_nogood_days,
     l_orders,
     r_excellent_days,
     r_fine_days,
     r_nogood_days,
     r_orders,
     oper_time)
    SELECT l_maxOper_date,
           '30天内',
           l_maxId + 1,
           aaa.city,
           aaa.excellent_days,
           aaa.fine_days,
           aaa.nogood_days,
           aaa.orders,
           ccc.l_excellent_days,
           ccc.l_fine_days,
           ccc.l_nogood_days,
           ccc.l_orders,
           bbb.r_excellent_days,
           bbb.r_fine_days,
           bbb.r_nogood_days,
           bbb.r_orders,
           sysdate
      FROM (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.excellent_days DESC) orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN TRUNC(l_maxOper_date) - 30 AND
                           TRUNC(l_maxOper_date)
                     group by city) aa) aaa,
           (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.r_excellent_days DESC) r_orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) r_excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) r_fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) r_nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN
                           ADD_MONTHS(TRUNC(l_maxOper_date), -1) - 30 AND
                           ADD_MONTHS(TRUNC(l_maxOper_date), -1)
                     group by city) aa) bbb,
           (SELECT aa.*,
                   ROW_NUMBER() OVER(ORDER BY aa.l_excellent_days DESC) l_orders
              FROM (SELECT city,
                           SUM(DECODE(grade, 'Ⅰ', 1, 0)) l_excellent_days,
                           SUM(DECODE(grade, 'Ⅱ', 1, 0)) l_fine_days,
                           SUM(DECODE(grade, 'Ⅰ', 0, 'Ⅱ', 0, 1)) l_nogood_days
                      FROM city_day a
                     WHERE oper_date BETWEEN
                           ADD_MONTHS(TRUNC(l_maxOper_date), -12) - 30 AND
                           ADD_MONTHS(TRUNC(l_maxOper_date), -12)
                     group by city) aa) ccc
     WHERE aaa.city = bbb.city(+)
       AND aaa.city = ccc.city(+);
     Commit;
  exception
  When Others Then
   l_errorcode := sqlerrm;
  Rollback;
END;

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;

  --当天空气质量最好的城市
  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;
  --最近30天内二级天数最多的城市
  select tt.city
    into l_air30daysII
    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;
  --最近1年内一级天数最多的城市
  select tt.city
    into l_air365daysI
    from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 365
             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 - 365
                         and t.oper_date < l_currentDay
                       group by t.city)
     and rownum = 1;
  --最近1年内二级天数最多的城市
  select tt.city
    into l_air365daysII
    from (select t.city, sum(decode(grade, 'Ⅱ', 1, 0)) total1
            from city_day t
           where t.oper_date > l_currentDay - 365
             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 - 365
                         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 || '#' ||
                       '二级天数最多的城市:' || l_air30daysII || '#' ||
                       '最近1年内空气质量一级天数最多的城市:' || l_air365daysI || '#' ||
                       '二级天数最多的城市:' || l_air365daysII
   where t.p_type = '1';
  Commit;
exception
  When Others Then
    l_errorcode := sqlerrm;
    Rollback;
  
end PROC_TO_T_PARAMETER;

CREATE   OR   REPLACE   PROCEDURE   WEBPM_SI_BROWSE 
    ( 
                  is_ulp                                             in         varchar2, 

                  os_seq                                             out       varchar2, 
                  os_si_code                                     out       varchar2, 
                  os_si_name                                     out       varchar2, 
                  os_unit_name                                 out       varchar2, 
                  os_ulp                                             out       varchar2, 
                  os_office_tel                               out       varchar2, 
                  os_fax                                             out       varchar2, 
                  os_address                                     out       varchar2, 
                  os_cont_type                                 out       varchar2, 
                  os_frame                                         out       varchar2, 
                  os_add_frame                                 out       varchar2, 
                  os_add_item                                   out       varchar2, 
                  os_sign_date                                 out       varchar2, 
                  os_contact_person                       out       varchar2, 
                  os_contact_tel                             out       varchar2, 
                  os_contact_msn                             out       varchar2, 
                  os_e_mail                                       out       varchar2, 
                  os_post_code                                 out       varchar2, 
                  os_eff_date                                   out       varchar2, 
                  os_arg_no                                       out       varchar2, 
                  os_year_flag                                 out       varchar2, 
                  os_es_prop                                     out       varchar2, 
                  os_ms_prop                                     out       varchar2, 
                  os_icspe_flag                               out       varchar2, 
                  os_icspe_list                               out       varchar2, 
                  os_icspe_amount                           out       varchar2, 
                  os_reason                                       out       varchar2, 
                  os_ms_set_mode                             out       varchar2, 
                  os_es_set_mode                             out       varchar2, 
                  os_register_man                           out       varchar2, 
                  os_register_date                         out       varchar2, 
                  os_remarks                                     out       varchar2, 
                  os_status                                       out       varchar2,         --   16   返回值 
                  os_result                                       out       varchar2           --   17   返回提示 
        ) 
      IS 

      ll_os_office_tel                           number; 
      ll_os_fax                                         number; 

      ll_os_sign_date                             date; 
      ll_os_eff_date                               date; 
      ll_os_icspe_amount                       number; 
      ll_os_register_date                     date; 

begin 


      begin 
          if   is_ulp   is   null   then 
              os_status   := '110 '; 
              os_result   :=   '单位法人不能为空 '; 
              return; 
          end   if; 
      end; 

                        os_status                           := '100 '; 
                        os_result                           := '开始查询局向信息 '   ; 
                        begin 
                          select 
                                seq                         , 
                                si_code                 , 
                                si_name                 , 
                                unit_name             , 
                                ulp                         , 
                                office_tel           , 
                                fax                         , 
                                address                 , 
                                cont_type             , 
                                frame                     , 
                                add_frame             , 
                                add_item               , 
                                sign_date             , 
                                contact_person   , 
                                contact_tel         , 
                                contact_msn         , 
                                e_mail                   , 
                                post_code             , 
                                eff_date               , 
                                arg_no                   , 
                                year_flag             , 
                                es_prop                 , 
                                ms_prop                 , 
                                icspe_flag           , 
                                icspe_list           , 
                                icspe_amount       , 
                                reason                   , 
                                ms_set_mode         , 
                                es_set_mode         , 
                                register_man       , 
                                register_date     , 
                                remarks 

                                into 

                                os_seq                                     , 
                                os_si_code                             , 
                                os_si_name                             , 
                                os_unit_name                         , 
                                os_ulp                                     , 
                                ll_os_office_tel                       , 
                                ll_os_fax                                     , 
                                os_address                             , 
                                os_cont_type                         , 
                                os_frame                                 , 
                                os_add_frame                         , 
                                os_add_item                           , 
                                ll_os_sign_date                         , 
                                os_contact_person               , 
                                os_contact_tel                     , 
                                os_contact_msn                   , 
                                os_e_mail                             , 
                                os_post_code                         , 
                                ll_os_eff_date                           , 
                                os_arg_no                               , 
                                os_year_flag                         , 
                                os_es_prop                             , 
                                os_ms_prop                           , 
                                os_icspe_flag                     , 
                                os_icspe_list                     , 
                                ll_os_icspe_amount                 , 
                                os_reason                             , 
                                os_ms_set_mode                   , 
                                os_es_set_mode                   , 
                                os_register_man                 , 
                                ll_os_register_date               , 
                                os_remarks 
                                from   WEBPM_SI   where   ulp   =   trim(is_ulp)   and   rownum=1; 
                                        os_status                           := '100 '; 
                                        os_result                           := 'hhh成功查询集成商信息!   集成商名称: '||is_ulp; 
                        exception 
                                    when   others   then 
                                              os_status     := '110 '; 
                                              os_result     := '无此集成商信息！   '||   is_ulp   || '     '||sqlerrm   ; 
                                              rollback; 
                        end; 
                      /**//*输出时间*/ 
          begin 
                        os_sign_date   :=     to_char(ll_os_sign_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误！ll_os_sign_date= '||ll_os_sign_date|| '     '||sqlerrm   ; 
                                          return; 
          end; 
          /**//*   集成商编码   */ 
          begin 
                os_office_tel:=to_number(ll_os_office_tel); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_office_tel=   '||ll_os_office_tel; 
          end; 
          begin 
                os_fax:=to_number(ll_os_fax); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_fax=   '||ll_os_fax; 
          end; 
          begin 
                os_icspe_amount:=to_number(ll_os_icspe_amount); 
          exception 
                when   others   then 
                  os_status                           := '110 '; 
                  os_result                           := '数据转换错误!   ll_os_icspe_amount=   '||ll_os_icspe_amount; 
          end; 
          begin 
                        os_eff_date   :=     to_char(ll_os_eff_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误！ll_os_eff_date= '||ll_os_eff_date|| '     '||sqlerrm   ; 
                                          return; 
          end; 
          begin 
                        os_register_date   :=     to_char(ll_os_register_date, 'yyyy/MM/dd   HH24:mi:ss '); 
          exception 
                                when   others   then 
                                          os_status                         := '110 '; 
                                          os_result                         := '数据转换错误！ll_os_register_date= '||ll_os_register_date|| '     '||sqlerrm   ; 
                                          return; 
          end; 


exception 
        when   others   then 
                return   ; 
end;   --   procedure