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