沉睡森林@漂在北京

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

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  152 随笔 :: 4 文章 :: 114 评论 :: 0 Trackbacks
drop package COMMON_FUNCTION
/

/*==============================================================*/
/* Database package: COMMON_FUNCTION                            */
/*==============================================================*/
create or replace package COMMON_FUNCTION as
   
function GETORIGINCODEBYID (ORIGINID In Numberreturn number;
   
function GETCOUNTYDEGREE (COUNTYID in Numberreturn Number;
   
function GETVILLAGENAMEBYID (VILLAGEID In Numberreturn Varchar2;
   
function GETTOWNNAMEBYID (TOWNID In Numberreturn Varchar2;
   
function GETCOUNTYNAMEBYID (COUNTYID In Numberreturn Varchar2;
   
function GETCOUNTYIDBYUSERID (USERID In Numberreturn NUMBER;
   
function GETWORKSTATIONIDBYUSERID (USERID In Numberreturn NUMBER;
   
function GETVILLAGECODEBYID (VILLAGEID In Numberreturn Varchar2;
   
function GETTOWNCODEBYID (TOWNID In Numberreturn Varchar2;
   
function GETCOUNTYCODEBYID (COUNTYID In Numberreturn Varchar2;
   
function GETWORKSTATIONNAMEBYID (WORKSTATIONID In Numberreturn Varchar2;
   
function GETCITYIDBYCOUNTYID (COUNTYID In Numberreturn Number;
   
function GETCOUNTYIDBYTOWNID (TOWNID In Numberreturn Number;
   
function GETHUBEIID return number;
   
function GETCUTTYPECODEBYID (CUTTYPEID In Numberreturn number;
   
function GETTREECODEBYID (TREEID In Numberreturn Varchar2;
   
function GETFORESTCODEBYID (FID In Numberreturn number;
end COMMON_FUNCTION;
/

create or replace package body COMMON_FUNCTION as
   
function GETORIGINCODEBYID (ORIGINID In Numberreturn number as
   lastNum 
number;
     
begin
            
select origin.ORIG_QYDM into lastNum from GG_ORIGIN origin where origin.ORIG_ID = originId;
          
return lastNum;
     
end;
   
function GETCOUNTYDEGREE (COUNTYID in Numberreturn Number as
   DegreeNum 
number;
     CountyCode 
varchar(50);
     
Begin
            
            
select ct.COUN_DM
          
into CountyCode 
          
from GG_County ct 
          
where ct.coun_id = CountyId;
          
          
--province  county
          if trim( Substr(trim(CountyCode),3) ) = '0000' then
                
--degreeNum := 0;
             return 0;
          
end if;
          
          
--city county
          if trim( Substr(trim(CountyCode),3) ) != '0000' and trim(Substr(trim(CountyCode),5)) = '00' then
                
--degreeNum := 1;
             return 1;
          
end if;
          
          
--county county
          if trim( Substr(trim(CountyCode),3) ) != '0000' and trim(Substr(trim(CountyCode),5)) != '00'then
                
--degreeNum := 2;
             return 2;
          
end if;
          
            
--return DegreeNum;
     ENd;
   
function GETVILLAGENAMEBYID (VILLAGEID In Numberreturn Varchar2 as
   villageName 
varchar2(50);
     
Begin
            
Select VILL_CM  
          
Into VillageName 
          
from GG_VILLAGE 
          
where VILL_ID = VillageId;
          
          
return VillageName;
     
End;
   
function GETTOWNNAMEBYID (TOWNID In Numberreturn Varchar2 as
   villageName 
varchar2(50);
     
Begin
            
Select TOWN_XZM  
          
Into VillageName 
          
from GG_Town 
          
where TOWN_ID = TownId;
          
          
return VillageName;
     
End;
   
function GETCOUNTYNAMEBYID (COUNTYID In Numberreturn Varchar2 as
   villageName 
varchar2(50);
     
Begin
            
Select COUN_MC  
          
Into VillageName 
          
from GG_County 
          
where COUN_ID = CountyId;
          
          
return VillageName;
     
End;
   
function GETCOUNTYIDBYUSERID (USERID In Numberreturn NUMBER as
   countyId 
NUMBER;
   
    
BEGIN
      
select  workstation.WORK_ID 
      
into countyId 
      
from  GG_COUNTY county,GG_USER u,GG_PERSON person,GG_WORKSTATION workstation
      
where u.USER_ID = userId
      
And  u.USER_RYID = person.PERS_ID
      
And  person.PERS_WORKID = workstation.WORK_ID
      
And workstation.WORK_XDMID = county.COUN_ID    ;
   
           
      
return countyId;
      
     
END;
   
function GETWORKSTATIONIDBYUSERID (USERID In Numberreturn NUMBER as
   workstationId 
NUMBER;
   
     
BEGIN
      
select  workstation.WORK_ID 
      
into workstationId 
      
from  GG_USER u,GG_PERSON person,GG_WORKSTATION workstation
      
where u.USER_ID = userId
      
And  u.USER_RYID = person.PERS_ID
      
And   person.PERS_WORKID = workstation.WORK_ID;
           
      
return workstationId;
      
      
END;
   
function GETVILLAGECODEBYID (VILLAGEID In Numberreturn Varchar2 as
   VillageCode 
varchar2(50);
     TownCode 
varchar2(50);
     CountyCode 
varchar2(50);
     
Begin 
             
select VILL_CDM Into VillageCode 
           
From  GG_VILLAGE 
           
where VILL_ID = VillageId;
                  
           
select town.TOWN_XZDM 
           
Into TownCode 
           
From  GG_Town town,GG_Village village  
           
Where   town.TOWN_ID = village.VILL_XZDMID 
           
And   village.VILL_ID = VillageId;
                        
                        
           
select county.COUN_DM 
           
Into CountyCode 
           
From   GG_County county,GG_town town,GG_village village 
           
Where village.VILL_ID = VillageId
           
And  town.TOWN_XDMID = county.COUN_ID
           
And   village.VILL_XZDMID = town.TOWN_ID;        
           
           
return CountyCode || TownCode || VillageCode;
           
     
End;
   
function GETTOWNCODEBYID (TOWNID In Numberreturn Varchar2 as
   TownCode 
varchar2(50);
     CountyCode 
varchar2(50);
     
Begin        
           
select town.TOWN_XZDM Into TownCode 
           
From  GG_Town town   
           
Where   town.TOWN_ID = TownId; 
                 
           
select county.COUN_DM Into CountyCode 
           
From  GG_County county,GG_town town  
           
Where   town.TOWN_ID = TownId
           
And  town.TOWN_XDMID = county.COUN_ID;        
            
           
return CountyCode  || TownCode || '000';
     
End;
   
function GETCOUNTYCODEBYID (COUNTYID In Numberreturn Varchar2 as
   CountyCode 
varchar2(50);
     
Begin 
             
--return 'hello';
           select county.COUN_DM Into CountyCode 
           
From  GG_County county
           
Where   county.COUN_ID = CountyId;
           
           
return CountyCode || '000'  || '000';
     
End;
   
function GETWORKSTATIONNAMEBYID (WORKSTATIONID In Numberreturn Varchar2 as
   workName 
varchar(80);
     
Begin
            
select workstation.WORK_DWM 
          
into workname 
          
from gg_workstation workstation 
          
where workstation.WORK_ID = WorkstationId;
          
            
return workName;
     
End;
   
function GETCITYIDBYCOUNTYID (COUNTYID In Numberreturn Number as
   CityId  
Number;
     
Begin
         
select   county.COUN_SJID  Into CityId 
       
From  GG_County county
       
Where  county.COUN_ID = CountyId; 
       
return cityId; 
     
End;
   
function GETCOUNTYIDBYTOWNID (TOWNID In Numberreturn Number as
   countyId 
Number;
     
Begin
            
select   town.TOWN_XDMID  Into  countyId 
          
From  GG_town town
          
where town.TOWN_ID = TownId;
          
          
return countyId;
     
End;
   
function GETHUBEIID return number as
   countyId 
number;
     
Begin
            
select   county.COUN_ID into countyId
          
from  gg_county county
          
where county.COUN_DM = '420000';
          
return countyId;
     
End;
   
function GETCUTTYPECODEBYID (CUTTYPEID In Numberreturn number as
   cutcode 
number;
     
Begin
           
select cuttype.CLAS_LXDM 
         
into cutcode  
         
from CF_CLASS cuttype 
         
where cuttype.CLAS_ID = cuttypeId;
         
         
return cutcode; 
     
End;
   
function GETTREECODEBYID (TREEID In Numberreturn Varchar2 as
   treeCode 
varchar2(50);
     
Begin
            
select tr.TRSP_SZDM 
          
into treeCode
          
from GG_TREESPECIES tr
          
where tr.TRSP_ID = treeId;
          
          
return treeCode;
     
End;
   
function GETFORESTCODEBYID (FID In Numberreturn number as
   lastNum 
number;
    
begin
        
select f.SOFO_LZDM into lastNum from GG_SORTSOFFOREST f where f.SOFO_ID = fId;
       
return lastNum;
    
end;
end COMMON_FUNCTION;
/
posted on 2009-05-21 14:35 王总兵 阅读(283) 评论(0)  编辑  收藏 所属分类: DataBase

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


网站导航: