函数的使用

create or replace function Fun_getinvclPK( cinvclasscode varchar2,cinvclasslev number)
return varchar2 is
fpk_invcl varchar(200) := '';
invclass_code varchar(20):='';
invclass_lev number :=0;
begin

invclass_code := trim(cinvclasscode);
invclass_code := substr(invclass_code,0,length(invclass_code)-2);
invclass_lev := cinvclasslev-1;
select pk_invcl into fpk_invcl from bd_invcl where invclasscode like invclass_code||'%' and invclasslev = invclass_lev;


return fpk_invcl;
end;


create or replace function Fun_getinvclInfo( cinvclasscode varchar2,cinvclasslev number)
return varchar2 is
fpk_invcl varchar(200) := '';
invclass_code varchar(20):='';
invclass_lev number :=0;
begin

invclass_code := trim(cinvclasscode);
invclass_code := substr(invclass_code,0,length(invclass_code)-2);
invclass_lev := cinvclasslev-1;
select pk_invcl into fpk_invcl from bd_invcl where invclasscode like invclass_code||'%' and invclasslev = invclass_lev;

select invclasscode into xx from bd_invcl where bd_invcl.pk_invcl = fpk_invcl;

return fpk_invcl;
end;


create or replace function f1(pk bd_corp.pk_corp%type) return bd_corp.unitcode%type
as
v_unitcode bd_corp.unitcode%type;
begin
select unitcode into v_unitcode from bd_corp where pk_corp=pk;
return v_unitcode;
end;


create or replace function Fun_getRInfo( cpkcorp varchar2,cflag number,cpk varchar2)
return varchar2 is
rinfo varchar(200) := '';
pkcorp varchar(20):='';
pk varchar(20):='';
flag number :=0;
begin

pkcorp := trim(cpkcorp);
flag := cflag;
pk := cpk;
--公司
if (flag = 0) then
select unitcode into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;
if (flag = 1) then
select unitname into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;
--商品分类
if (flag = 3) then
select invclasscode into rinfo from bd_invcl where pk_corp=pkcorp and nvl(dr,0)=0 and pk_invcl = pk;
end if;



return rinfo;
end;

select Fun_getRInfo('1114',5,'') aa ,Fun_getRInfo('1004',1,'') bb from dual;


select * from bd_invcl


create or replace function Fun_getRInfo( cpkcorp varchar2,cflag number,cpk varchar2)
return varchar2 is
rinfo varchar(200) := '';
pkcorp varchar(20):='';
pk varchar(20):='';
flag number :=0;
begin

pkcorp := trim(cpkcorp);
flag := cflag;
pk := cpk;
--公司编码
if (flag = 0) then
if (pkcorp=='0001') then
unitcode='0001'
else
select unitcode into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;

end if;
--公司名称
if (flag = 1) then
if (pkcorp=='0001') then
unitcode='0001'
else
select unitname into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;

end if;
--商品分类编码
if (flag = 2) then
select invclasscode into rinfo from bd_invcl where nvl(dr,0)=0 and pk_invcl = pk;
end if;

--商品分类名称
if (flag = 3) then
select invclassname into rinfo from bd_invcl where nvl(dr,0)=0 and pk_invcl = pk;
end if;

--主计量单位编码
if (flag = 4) then
select shortname into rinfo from bd_measdoc where nvl(dr,0)=0 and pk_measdoc = pk;
end if;

--主计量单位名称
if (flag = 5) then
select measname into rinfo from bd_measdoc where nvl(dr,0)=0 and pk_measdoc = pk;
end if;

--自定义项档案列表名称
if (flag = 6) then
select doclistname into rinfo from bd_defdoclist where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--自定义项档案列表编码
if (flag = 7) then
select doclistcode into rinfo from bd_defdoclist where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--基础数据资源表名称
if (flag = 6) then
select bdname into rinfo from bd_bdinfo where nvl(dr,0)=0 and pk_bdinfo= pk;
end if;

--基础数据资源表编码
if (flag = 7) then
select bdcode into rinfo from bd_bdinfo where nvl(dr,0)=0 and pk_bdinfo= pk;
end if;

return rinfo;
end;

--商品税率
create or replace function Fun_gettptaxiInfo(ctypetaxitempk varchar2,cinvclpk varchar2,cflag number)
return varchar2 is typettinfo varchar(200) :='';
cfpk varchar2(20):='';
cinvpk varchar2(20):='';
flag number :=0;
begin
cfpk:=ctypetaxitempk;
cinvpk:=cinvclpk;
flag:=cflag;
if(flag=0) then
select unitcode into typettinfo from bd_corp where pk_corp=(select pk_corp from zm_typetaxitem_h where pk_typetaxitem_h=cfpk and nvl(dr,0)=0) and nvl(dr,0)=0;
end if;
if(flag=1) then
select unitname into typettinfo from bd_corp where pk_corp=(select pk_corp from zm_typetaxitem_h where pk_typetaxitem_h=cfpk and nvl(dr,0)=0) and nvl(dr,0)=0;
end if;
if(flag=2) then
select invclasscode into typettinfo from bd_invcl where pk_invcl=cinvpk and nvl(dr,0)=0;
end if;
if(flag=3) then
select invclassname into typettinfo from bd_invcl where pk_invcl=cinvpk and nvl(dr,0)=0;
end if;
return typettinfo ;
end;

--获取上级商品分类编码及名称
create or replace function Fun_getinvclInfo( cinvclasscode varchar2,cinvclasslev number,cflag number)
return varchar2 is
finvclinfo varchar(200) := '';
invclass_code varchar(20):='';
invclass_lev number :=0;
flag number :=0;
begin
invclass_code := trim(cinvclasscode);
invclass_code := substr(invclass_code,0,length(invclass_code)-2);
invclass_lev := cinvclasslev-1;
if(flag=0) then
select invclassname into finvclinfo from bd_invcl where invclasscode like invclass_code||'%' and invclasslev = invclass_lev;
else
finvclinfo:=invclass_code;
end if;

return finvclinfo;
end;

Fun_getRInfo(a.pk_corp, 0, '') as unitcode,Fun_getRInfo(a.pk_corp, 1, '') as unitname,Fun_getinvclInfo(a.invclasscode,a.invclasslev,0) as fatherinvclassname, Fun_getinvclInfo(a.invclasscode,a.invclasslev,1) as fatherinvclass_code

create or replace function Fun_getinvclInfo( cinvclasscode varchar2,cinvclasslev number,cflag number)
return varchar2 is
finvclinfo varchar(200) := '';
invclass_code varchar(20):='';
invclass_lev number :=0;
flag number :=0;
begin
invclass_code := trim(cinvclasscode);
invclass_code := substr(invclass_code,0,length(invclass_code)-2);
invclass_lev := cinvclasslev-1;
if(flag=0) then
select invclassname into finvclinfo from bd_invcl where invclasscode like invclass_code||'%' and invclasslev = invclass_lev;
end if
if(flag=1) then
finvclinfo:=invclass_code;
end if;

return finvclinfo;
end;

create or replace function Fun_getRInfo( cpkcorp varchar2,cflag number,cpk varchar2)
return varchar2 is
rinfo varchar(200) := '';
pkcorp varchar(20):='';
pk varchar(20):='';
flag number :=0;
begin

pkcorp := trim(cpkcorp);
flag := cflag;
pk := cpk;
--公司编码
if (flag = 0) then
if (pkcorp='0001') then
rinfo :='0001';
else
select unitcode into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;

end if;
--公司名称
if (flag = 1) then
if (pkcorp='0001') then
rinfo :='0001';
else
select unitname into rinfo from bd_corp where pk_corp=pkcorp and nvl(dr,0)=0;
end if;

end if;
--商品分类编码
if (flag = 2) then
select invclasscode into rinfo from bd_invcl where nvl(dr,0)=0 and pk_invcl = pk;
end if;

--商品分类名称
if (flag = 3) then
select invclassname into rinfo from bd_invcl where nvl(dr,0)=0 and pk_invcl = pk;
end if;

--主计量单位编码
if (flag = 4) then
select shortname into rinfo from bd_measdoc where nvl(dr,0)=0 and pk_measdoc = pk;
end if;

--主计量单位名称
if (flag = 5) then
select measname into rinfo from bd_measdoc where nvl(dr,0)=0 and pk_measdoc = pk;
end if;

--自定义项档案列表名称
if (flag = 6) then
select doclistname into rinfo from bd_defdoclist where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--自定义项档案列表编码
if (flag = 7) then
select doclistcode into rinfo from bd_defdoclist where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--父级自定义项档案表名称
if (flag = 8) then
select docname into rinfo from bd_defdoc where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--父级自定义项档案表编码
if (flag = 9) then
select doccode into rinfo from bd_defdoc where nvl(dr,0)=0 and pk_defdoclist = pk;
end if;

--基础数据资源表名称
if (flag = 10) then
select bdname into rinfo from bd_bdinfo where nvl(dr,0)=0 and pk_bdinfo= pk;
end if;

--基础数据资源表编码
if (flag = 11) then
select bdcode into rinfo from bd_bdinfo where nvl(dr,0)=0 and pk_bdinfo= pk;
end if;

--产品线档案名称
if (flag = 12) then
select prodlinename into rinfo from bd_prodline where nvl(dr,0)=0 and pk_prodline= pk;
end if;

--产品线档案编码
if (flag = 13) then
select prodlinecode into rinfo from bd_prodline where nvl(dr,0)=0 and pk_prodline= pk;
end if;

--税目税率名称
if (flag = 14) then
select taxname into rinfo from bd_taxitems where nvl(dr,0)=0 and pk_taxitems= pk;
end if;

--税目税率编码
if (flag = 15) then
select taxcode into rinfo from bd_taxitems where nvl(dr,0)=0 and pk_taxitems= pk;
end if;

return rinfo;
end;