沉睡森林@漂在北京

          本處文章除注明“轉載”外均為原創,轉載請注明出處。

            BlogJava :: 首頁 :: 新隨筆 :: 聯系 :: 聚合  :: 管理 ::
            152 隨筆 :: 4 文章 :: 114 評論 :: 0 Trackbacks
          drop package TRANSPORT_CHECK
          /

          /*==============================================================*/
          /* Database package: TRANSPORT_CHECK                            */
          /*==============================================================*/
          create or replace package TRANSPORT_CHECK as
             type OUTLIST 
          is REF CURSOR;
             
          procedure PROCEXCEL (YEARNUM In NUMBER,COUNTYID In Number,RESULT Out outlist);
             
          function LISTPROVINCE (YEARNUM In Number,COUNTYID In Numberreturn outlist;
             
          function LISTCITY (YEARNUM In Number,COUNTYID In Numberreturn outlist;
             
          function LISTCOUNTY (YEARNUM In Number,COUNTYID In Numberreturn outlist;
          end TRANSPORT_CHECK;
          /

          create or replace package body TRANSPORT_CHECK as
             
          procedure PROCEXCEL (YEARNUM In NUMBER,COUNTYID In Number,RESULT Out outlist) as
             
          BEGIN
                 
          --Open result for
                 
                 
          if Common_Function.GetCountyDegree(CountyId) = 0 then
                    result :
          = listProvince(YearNum,CountyId);
                 
          end if;
                 
                     
                 
          if Common_Function.GetCountyDegree(CountyId) = 1 then
                    result :
          = listCity(YearNum,CountyId);
                 
          end if;
                 
                     
                 
          if Common_Function.GetCountyDegree(CountyId) = 2 then
                    result :
          = listCounty(YearNum,CountyId);
                 
          end if;
                 
               
          END;
             
          function LISTPROVINCE (YEARNUM In Number,COUNTYID In Numberreturn outlist as
             rc outlist;
               
          Begin
                      
          open rc for
                    
          select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
              
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT 1884 county,'' cityid,
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men  
               
          FROM ys_checkregister chrg
               
          where   chrg_year=YearNum
               
          group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
               
          group by county
               
               
          union
               
               
          select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
              
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT common_function.GetCityIdByCountyId(chrg.chrg_county) county,'' cityid,
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men  
               
          FROM ys_checkregister chrg
               
          where   chrg_year=YearNum
               
          group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
               
          group by county
               
               
          order by 1 asc;
                    
                    
                    
          return rc;
               
          End;
             
          function LISTCITY (YEARNUM In Number,COUNTYID In Numberreturn outlist as
             rc outlist;
               
          Begin  
                    
          open rc for
                   
                   
          select county, common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
              
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT common_function.GetCityIdByCountyId(chrg.chrg_county) county,'' cityid,
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men  
               
          FROM ys_checkregister chrg
               
          where common_function.GetCityIdByCountyId(chrg.chrg_county)=CountyId and chrg_year=YearNum
               
          group by common_function.GetCityIdByCountyId(chrg.chrg_county) ,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
               
          group by county
               
               
          union
               
               
               
          select county,common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
             
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT  chrg.chrg_county county, common_function.GetCityIdByCountyId(chrg.chrg_county) cityid,
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men  
               
          FROM ys_checkregister chrg
              
          where common_function.GetCityIdByCountyId(chrg.chrg_county)=CountyId and chrg_year=YearNum
               
          group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
               
          group by county
              
               ;
               
                  
                  
          return rc;
               
          End;
             
          function LISTCOUNTY (YEARNUM In Number,COUNTYID In Numberreturn outlist as
             rc outlist;
               
          Begin 
                   
          open rc for 
                  
                    
          select county,common_function.getCountyNameById(county) countyname,'' wkid,'' wkname,sum(chnum) chum,
             
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT  chrg.chrg_county county, 
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men  
               
          FROM ys_checkregister chrg
               
          where chrg.chrg_county =CountyId   and chrg_year=YearNum
               
          group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
                
          group by county 
                
                
          union
              
               
          select county,common_function.getCountyNameById(county) countyname,wkid,common_function.GetWorkstationNameById(wkid) wkname,
              
          sum(chnum) chum,
             
             
          sum(wzid_wz) wznum,sum(wzid_zhbf) zhbfnum,sum(wzid_wxz) wxznum,sum(wzid_tgwz) tgwznum,sum(wzid_ty) tynum,sum(wzid_qt) qtnum,
              
          sum(Swood) woodcubage,sum(Sbamboo) bambooCubage,sum(Sother) otherCubage,sum(frpass) frpass,
              
          sum(fmon) moneyCount,sum(fmoney) moneyNum,sum(fsave) saveCount,sum(fsavenum) saveNum, sum(men) menNum
              
          from 
              ( 
          SELECT  chrg.chrg_county county, 
                     To_char(common_function.getWorkstationidByUserId(chrg_userid)) 
          as wkid,  
                     
          count(*as  chnum,
                     decode(chrg_wzid,
          '1'count(*)) as  wzid_wz, decode(chrg_wzid,'2'count(*)) as  wzid_zhbf,
                     decode(chrg_wzid,
          '3'count(*)) as  wzid_wxz, decode(chrg_wzid,'4'count(*)) as  wzid_tgwz,
                     decode(chrg_wzid,
          '5'count(*)) as  wzid_ty, decode(chrg_wzid,'6'count(*)) as  wzid_qt,
                        
                     
          sum(chrg.chrg_wztree) Swood, sum(chrg.chrg_wzbamboo) Sbamboo,  sum(chrg.chrg_wzother) Sother,
                     decode(chrg_frpass,
          '1'count(*)) as  frpass,decode(chrg.chrg_fmon,'1'count(*)) as  fmon,
                     decode(chrg.chrg_fsave,
          '1'count(*)) as  fsave,
                     
          sum(chrg.chrg_fmoney) fmoney, sum( chrg.chrg_fsavenum) fsavenum,
                     
          sum( chrg.chrg_mennum) men
               
          FROM ys_checkregister chrg
               
          where chrg.chrg_county =CountyId and chrg_year=YearNum
               
          group by chrg.chrg_county,common_function.getWorkstationidByUserId(chrg_userid),chrg_wzid,chrg_frpass,chrg.chrg_fmon,chrg.chrg_fsave)
                
          group by county,wkid
                
              
          order by 3 desc
               
              ;
                 
                 
          return rc;
               
          End;
          end TRANSPORT_CHECK;
          /
          posted on 2009-05-21 14:38 王總兵 閱讀(300) 評論(0)  編輯  收藏 所屬分類: DataBase
          主站蜘蛛池模板: 当雄县| 蓬莱市| 云阳县| 太湖县| 阳东县| 华容县| 社旗县| 廉江市| 吉隆县| 汉源县| 陆丰市| 弥渡县| 江源县| 万源市| 绥宁县| 林口县| 略阳县| 临汾市| 惠水县| 鄂温| 当雄县| 偏关县| 昭苏县| 鄂托克前旗| 都昌县| 库尔勒市| 尉氏县| 石棉县| 广宁县| 大名县| 保亭| 吐鲁番市| 湘西| 华坪县| 资阳市| 杭州市| 南汇区| 连南| 桃江县| 天津市| 黄冈市|