函数的使用

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 = 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;