沉睡森林@漂在北京

本处文章除注明“转载”外均为原创,转载请注明出处。

  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 王总兵 阅读(128) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: