自己在PL/SQL里写了两个函数A、B，请问如何在A中调用B，也就是嵌套调用！

我在A体内直接写下B(..);
却提示：PLS－00221：'B'不是过程或尚未定义。

10 个解决方案

#1

如果A，B在同一个包体内C，那么直接在C包体内的A函数中调用B，也就是B（..）

如果A在C包体内，B在D包体内，那么在C包体内的A函数中调用D包体内中的B，需添加前缀包名，也就是
D.B（..）

还有如果AB不在同一个用户下，就需添加用户名，比如 userName.D.B(..)

#2

是独立的函数，我没有做成包，能调用吗？

#3

可以调用是不是同一个用户建的？

#4

A，B函数是不是都有效，是不是在同一个用户下

#5

都是同一用户下，都能独立运行，当然是有效的

#6

直接把你的代码贴出来啊。是不是你没有把B(...)的返回值赋给一个变量？

#7

TO:fuxia(mike)
帮我看看！

CREATE OR REPLACE FUNCTION f_KePYJS_new(
as_YueF IN tKePTJYB.YueF%TYPE,
as_QiSRQ IN tKePTJYB.QiSRQ%TYPE,
as_JieZRQ IN tKePTJYB.JieZRQ%TYPE,
as_CaoZYH IN tKePTJYB.CaoZYH%TYPE
) RETURN Integer IS
  Result INTEGER;
  rec_tKePTJYB tKePTJYB%ROWTYPE;
  ls_shangy char(6);

CURSOR cur_KePLX IS
select keplx,piaoj from (
select keplx,piaoj
  from tkeptz
where riq between as_qisrq and as_jiezrq
union
select '0' keplx,piaoj
  from tbufpsj
where riq between as_qisrq and as_jiezrq
union
select '2' keplx,tkepsc.piaoj
  from tshoupdjgl,tkepsc
where tshoupdjgl.jilh = tkepsc.jilh
   and tshoupdjgl.jiesrq between as_qisrq and as_jiezrq
   and substr(tshoupdjgl.xianlh,1,2) not in ('21')
   union
select '1' keplx,tkepsc.piaoj
  from tshoupdjgl,tkepsc
where tshoupdjgl.jilh = tkepsc.jilh
   and tshoupdjgl.jiesrq between as_qisrq and as_jiezrq
   and substr(tshoupdjgl.xianlh,1,2) in('21')----'21'ÎªÓÐÈËµ¥¼¶Æ±
   union
select keplx,piaoj
  from tkeptjyb
group by keplx,piaoj
   )
group by keplx,piaoj;

  rec_KePLX cur_KePLX%ROWTYPE;

BEGIN
  rec_tKePTJYB.YueF:=as_YueF;
  rec_tKePTJYB.QiSRQ:=as_QiSRQ;
  rec_tKePTJYB.JieZRQ:=as_JieZRQ;
  rec_tKePTJYB.CaoZYH:=as_CaoZYH;
  rec_tKePTJYB.CaoZSJ:=SYSDATE;

  FOR rec_KePLX IN cur_KePLX Loop
    rec_tKePTJYB.KePLX:=rec_KePLX.KePLX;
    rec_tKePTJYB.PiaoJ:=rec_KePLX.PiaoJ;

    SELECT NVL(SUM(ShuL*DECODE(caozlx,'1',1,0)),0), --±¾ÔÂÁìÓÃµÄÊýÁ¿
        NVL(SUM(ShuL*DECODE(caozlx,'2',1,0)),0), --±¾ÔÂ×ªÈëµÄÊýÁ¿
        NVL(SUM(ShuL*DECODE(caozlx,'3',1,0)),0), --±¾ÔÂ×ª³öµÄÊýÁ¿
        NVL(SUM(ShuL*DECODE(caozlx,'4',1,0)),0), --±¾ÔÂÒÅÊ§µÄÊýÁ¿
        NVL(SUM(ShuL*DECODE(caozlx,'5',1,0)),0)  --±¾ÔÂ±¨·ÏµÄÊýÁ¿
      INTO rec_tKePTJYB.BenYLY,
           rec_tKePTJYB.BenYZR,
           rec_tKePTJYB.BenYZC,
           rec_tKePTJYB.BenYYS,
           rec_tKePTJYB.BaoFP
      FROM tKePTZ
     WHERE KePLX=rec_tKePTJYB.KePLX AND PiaoJ=rec_tKePTJYB.PiaoJ
       AND RiQ BETWEEN rec_tKePTJYB.QiSRQ AND rec_tKePTJYB.JieZRQ;

    If rec_tKePTJYB.KePLX='0' Then --²¹·£Æ±
      SELECT NVL(Sum(ShuL),0)
        INTO rec_tKePTJYB.BenYSC
        FROM tBuFPSJ
       WHERE PiaoJ=rec_tKePTJYB.PiaoJ
         AND RiQ BETWEEN rec_tKePTJYB.QiSRQ AND rec_tKePTJYB.JieZRQ;
    Elsif rec_tKePTJYB.KePLX='2' then--¶à¼¶Æ±
      SELECT NVL(Sum(K.ShuL),0)
        INTO rec_tKePTJYB.BenYSC
        FROM tShouPDJGL S,tKePSC K
       WHERE S.JiLH=K.JiLH
         AND Substr(S.XianLh,2,1)=2
         AND S.CaoZLX='1'
         AND K.PiaoJ=rec_tKePTJYB.PiaoJ
         AND S.RiQ BETWEEN rec_tKePTJYB.QiSRQ AND rec_tKePTJYB.JieZRQ;
    Else
      SELECT NVL(Sum(K.ShuL),0)
        INTO rec_tKePTJYB.BenYSC
        FROM tShouPDJGL S,tKePSC K
       WHERE S.JiLH=K.JiLH
         AND Substr(S.XianLh,2,1)<>2
         AND S.CaoZLX='1'
         AND K.PiaoJ=rec_tKePTJYB.PiaoJ
         AND S.RiQ BETWEEN rec_tKePTJYB.QiSRQ AND rec_tKePTJYB.JieZRQ;
    End If;

    select nvl(max(yuef),0) into ls_shangy
    from tkeptjyb
    where keplx=rec_tKePTJYB.KePLX
    and piaoj=rec_tKePTJYB.PiaoJ;

    if ls_shangy='0' then
       rec_tKePTJYB.ShangYJC := 0;
    else
        select nvl(sum(benyjc),0) into rec_tKePTJYB.ShangYJC
        from tkeptjyb
        where yuef=ls_shangy
         and keplx=rec_tKePTJYB.KePLX
         and piaoj=rec_tKePTJYB.PiaoJ;
    end if;

    rec_tKePTJYB.BenYLY:=Nvl(rec_tKePTJYB.BenYLY, 0);
    rec_tKePTJYB.BenYZR:=Nvl(rec_tKePTJYB.BenYZR, 0);
    rec_tKePTJYB.BenYZC:=Nvl(rec_tKePTJYB.BenYZC, 0);
    rec_tKePTJYB.BenYYS:=Nvl(rec_tKePTJYB.BenYYS, 0);
    rec_tKePTJYB.BaoFP:=Nvl(rec_tKePTJYB.BaoFP, 0);
    rec_tKePTJYB.BenYSC:=Nvl(rec_tKePTJYB.BenYSC, 0);
    rec_tKePTJYB.ShangYJC:=Nvl(rec_tKepTJYB.ShangYJC, 0);
    rec_tKePTJYB.BenYJC:=rec_tKePTJYB.ShangYJC+rec_tKePTJYB.BenYLY+rec_tKePTJYB.BenYZR
            -(rec_tKePTJYB.BenYZC+rec_tKePTJYB.BenYYS+rec_tKePTJYB.BaoFP+rec_tKePTJYB.BenYSC);

    INSERT INTO tKePTJYB (YueF, KePLX, PiaoJ, QiSRQ, JieZRQ,
       ShangYJC, BenYLY, BenYSC, BenYZR, BenYZC, BenYJC, BenYYS, BaoFP,
       CaoZSJ, CaoZYH)
       VALUES (rec_tKePTJYB.YueF, rec_tKePTJYB.KePLX, rec_tKePTJYB.PiaoJ, rec_tKePTJYB.QiSRQ, rec_tKePTJYB.JieZRQ,
          rec_tKePTJYB.ShangYJC, rec_tKePTJYB.BenYLY, rec_tKePTJYB.BenYSC, rec_tKePTJYB.BenYZR, rec_tKePTJYB.BenYZC, rec_tKePTJYB.BenYJC, rec_tKePTJYB.BenYYS, rec_tKePTJYB.BaoFP,
       rec_tKePTJYB.CaoZSJ, rec_tKePTJYB.CaoZYH);

  END LOOP;
  COMMIT;

  Return(0);

EXCEPTION
  WHEN OTHERS THEN
    Result:=SQLCode;
    ROLLBACK;
    If cur_KePLX%IsOpen Then
      CLOSE cur_KePLX;
    End If;
    Return(Result);
END f_KePYJS_new;
－－－－－－－－－－－

#8

CREATE OR REPLACE FUNCTION f_KePYJS_Hesxl(
as_YueF IN tKePTJYB.YueF%TYPE,
as_QiSRQ IN tKePTJYB.QiSRQ%TYPE,
as_JieZRQ IN tKePTJYB.JieZRQ%TYPE,
as_CaoZYH IN tKePTJYB.CaoZYH%TYPE
) RETURN Integer IS

Result INTEGER;
/*¿ÍÆ±ÔÂ½áËã£¬Éú³É¿ÍÆ±Í³¼ÆÔÂ±¨±í*/
  ls_Hesxl varchar2(6):='';
  ls_BiaoZ Char(4);
  ls_Step number(3):=15;
  ls_shangy char(6);
  rec_tKePTJYB_DL tKePTJYB%ROWTYPE;

  CURSOR cur_KePLX_DL(as_xianldm tKeptjyb.Xianldm%type) IS
      select keplx,piaoj,xianldm from (
       select keplx,piaoj,xianldm
           from tkeptz
           where riq between as_qisrq and as_jiezrq and xianldm<>'0000'
       union
      select distinct '2' keplx,tkepsc.piaoj,substr(xianlh,3,4)as xianldm
           from tshoupdjgl,tkepsc
           where tshoupdjgl.jilh = tkepsc.jilh
           and tshoupdjgl.jiesrq between as_qisrq and as_jiezrq
           and substr(tshoupdjgl.xianlh,1,2) not in ('21') and substr(xianlh,3,4)<>'0000'
       union
      select keplx,piaoj,xianldm
           from tkeptjyb where xianldm<>'0000'
           group by keplx,piaoj,xianldm
       )
       where xianldm=as_xianldm
       group by keplx,piaoj,xianldm;


  rec_KePLX_DL cur_KePLX_DL%ROWTYPE;

begin
   rec_tKePTJYB_DL.YueF:=as_YueF;
   rec_tKePTJYB_DL.QiSRQ:=as_QiSRQ;
   rec_tKePTJYB_DL.JieZRQ:=as_JieZRQ;
   rec_tKePTJYB_DL.CaoZYH:=as_CaoZYH;
   rec_tKePTJYB_DL.CaoZSJ:=SYSDATE;

   select NVL(substr(cansz,ls_Step,4),'ZZZZ') into ls_BiaoZ from tCansxx where cansx='Hesxl';

   while ls_BiaoZ<>'ZZZZ' Loop
     ls_Hesxl:=ls_BiaoZ;

     dbms_output.put_line(ls_Hesxl);

     for rec_KePLX_DL in cur_KePLX_DL(ls_Hesxl) Loop
        rec_tKePTJYB_DL.KePLX:=rec_KePLX_DL.KePLX;
        rec_tKePTJYB_DL.PiaoJ:=rec_KePLX_DL.PiaoJ;
        rec_tKePTJYB_DL.Xianldm:=rec_KePLX_DL.Xianldm;

        SELECT NVL(SUM(ShuL*DECODE(caozlx,'1',1,0)),0), --±¾ÔÂÁìÓÃµÄÊýÁ¿
         NVL(SUM(ShuL*DECODE(caozlx,'2',1,0)),0), --±¾ÔÂ×ªÈëµÄÊýÁ¿
         NVL(SUM(ShuL*DECODE(caozlx,'3',1,0)),0), --±¾ÔÂ×ª³öµÄÊýÁ¿
         NVL(SUM(ShuL*DECODE(caozlx,'4',1,0)),0), --±¾ÔÂÒÅÊ§µÄÊýÁ¿
         NVL(SUM(ShuL*DECODE(caozlx,'5',1,0)),0)  --±¾ÔÂ±¨·ÏµÄÊýÁ¿
          INTO rec_tKePTJYB_DL.BenYLY,
           rec_tKePTJYB_DL.BenYZR,
           rec_tKePTJYB_DL.BenYZC,
           rec_tKePTJYB_DL.BenYYS,
           rec_tKePTJYB_DL.BaoFP
          FROM tKePTZ
          WHERE KePLX=rec_tKePTJYB_DL.KePLX AND PiaoJ=rec_tKePTJYB_DL.PiaoJ AND Xianldm=rec_tKePTJYB_DL.Xianldm
             AND RiQ BETWEEN rec_tKePTJYB_DL.QiSRQ AND rec_tKePTJYB_DL.JieZRQ;


  dbms_output.put_line('±¾ÔÂÁìÓÃ£º'||rec_tKePTJYB_DL.BenYLY||'  ±¾ÔÂ×ªÈë£º'|| rec_tKePTJYB_DL.BenYZR||'¡¡±¾ÔÂ×ª³ö£º'||rec_tKePTJYB_DL.BenYZC||'¡¡±¾ÔÂÒÅÊ§£º'||rec_tKePTJYB_DL.BenYYS||'¡¡±¨·ÏÆ±£º'|| rec_tKePTJYB_DL.BaoFP);

        IF rec_tKePTJYB_DL.KePLX='2' then--¶à¼¶Æ±
            SELECT NVL(Sum(K.ShuL),0)
            INTO rec_tKePTJYB_DL.BenYSC
            FROM tShouPDJGL S,tKePSC K
            WHERE S.JiLH=K.JiLH
              AND Substr(S.XianLh,2,1)=2
              AND S.CaoZLX='1'
              AND K.PiaoJ=rec_tKePTJYB_DL.PiaoJ AND substr(Xianlh,3,4)= rec_tKePTJYB_DL.Xianldm
              AND S.RiQ BETWEEN rec_tKePTJYB_DL.QiSRQ AND rec_tKePTJYB_DL.JieZRQ;
         End If;


         select nvl(max(yuef),0) into ls_shangy
         from tkeptjyb
         where keplx=rec_tKePTJYB_DL.KePLX
         and xianldm=rec_tKePTJYB_DL.Xianldm
          and piaoj=rec_tKePTJYB_DL.PiaoJ;

          if ls_shangy='0' then
            rec_tKePTJYB_DL.ShangYJC := 0;
          else
            select nvl(sum(benyjc),0) into rec_tKePTJYB_DL.ShangYJC
            from tkeptjyb
            where yuef=ls_shangy
            and keplx=rec_tKePTJYB_DL.KePLX
            and xianldm=rec_tKePTJYB_DL.Xianldm
            and piaoj=rec_tKePTJYB_DL.PiaoJ;
          end if;

dbms_output.put_line('ÉÏÔÂ½á´æ£º'||rec_tkeptjyb_DL.shangyjc||chr(13));

          rec_tKePTJYB_DL.BenYLY:=Nvl(rec_tKePTJYB_DL.BenYLY, 0);
          rec_tKePTJYB_DL.BenYZR:=Nvl(rec_tKePTJYB_DL.BenYZR, 0);
          rec_tKePTJYB_DL.BenYZC:=Nvl(rec_tKePTJYB_DL.BenYZC, 0);
          rec_tKePTJYB_DL.BenYYS:=Nvl(rec_tKePTJYB_DL.BenYYS, 0);
          rec_tKePTJYB_DL.BaoFP:=Nvl(rec_tKePTJYB_DL.BaoFP, 0);
          rec_tKePTJYB_DL.BenYSC:=Nvl(rec_tKePTJYB_DL.BenYSC, 0);
          rec_tKePTJYB_DL.ShangYJC:=Nvl(rec_tKepTJYB_DL.ShangYJC, 0);
          rec_tKePTJYB_DL.BenYJC:=rec_tKePTJYB_DL.ShangYJC+rec_tKePTJYB_DL.BenYLY+rec_tKePTJYB_DL.BenYZR
       -(rec_tKePTJYB_DL.BenYZC+rec_tKePTJYB_DL.BenYYS+rec_tKePTJYB_DL.BaoFP+rec_tKePTJYB_DL.BenYSC);

       INSERT INTO tKePTJYB (YueF, KePLX, PiaoJ, QiSRQ, JieZRQ,
       ShangYJC, BenYLY, BenYSC, BenYZR, BenYZC, BenYJC, BenYYS, BaoFP,
       CaoZSJ, CaoZYH, Xianldm)
       VALUES (rec_tKePTJYB_DL.YueF, rec_tKePTJYB_DL.KePLX, rec_tKePTJYB_DL.PiaoJ, rec_tKePTJYB_DL.QiSRQ, rec_tKePTJYB_DL.JieZRQ,
          rec_tKePTJYB_DL.ShangYJC, rec_tKePTJYB_DL.BenYLY, rec_tKePTJYB_DL.BenYSC, rec_tKePTJYB_DL.BenYZR, rec_tKePTJYB_DL.BenYZC, rec_tKePTJYB_DL.BenYJC, rec_tKePTJYB_DL.BenYYS, rec_tKePTJYB_DL.BaoFP,
       rec_tKePTJYB_DL.CaoZSJ, rec_tKePTJYB_DL.CaoZYH,rec_tKePTJYB_DL.Xianldm);

       END LOOP;

     ls_Step:=ls_Step+14;
     select NVL(substr(cansz,ls_Step,4),'ZZZZ') into ls_BiaoZ from tCansxx where cansx='Hesxl';
    END LOOP;

    COMMIT;
    RETURN (0);

    EXCEPTION
        WHEN OTHERS THEN
        Result:=SQLCode;
         ROLLBACK;
       If cur_KePLX_DL%IsOpen Then
          CLOSE cur_KePLX_DL;
       End If;
      Return(Result);


END;

请问怎么在f_KePYJS_new里调用f_KePYJS_Hesxl？？？？

#9

在f_KeyPYJS_new中添加：

v_F_KeyPYJS_Hesx1 Integer;
v_F_KeyPYJS_Hesx1:=f_KePYJS_Hesxl(as_YueF,as_QiSRQ,as_JieZRQ,as_CaoZYH);

然后值v_F_KeyPYJS_Hesx1就可以用了。

#10

是的，我试了，运行也没报错，但是f_KepYjs_Hesxl并没有执行！

#1

#2