CREATE OR REPLACE FUNCTION GJERP_MUCOUNT(MT IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN ((SELECT SUM(QTYSTU_0)
FROM STOCK
WHERE ITMREF_0 =MT AND STA_0 IN ('A1','A2','A3') GROUP BY ITMREF_0)-
(SELECT SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0)
FROM MFGOPE INNER JOIN MFGMAT
ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0
WHERE MFGMAT.MATSTA_0< =2 AND MFGOPE.XMMSTA_0=2 AND MFGMAT.ITMREF_0=MT
GROUP BY MFGMAT.ITMREF_0));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
上述RETURN中的两个SELECT执行后都能返回一个结果。但是函数编译出如下错:FUNCTION GJERP.GJERP_MUCOUNT 编译错误错误:PLS-00103: 出现符号 "SELECT"在需要下列之一时:
( - + case
mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<一个带有字符集说明的可带引号的字符串文字>
<一个可带引号的 SQL 字符串>
行:4
文本:RETURN ((SELECT SUM(QTYSTU_0)错误:PLS-00103: 出现符号 ")"在需要下列之一时:
. ( , * @ % & -
+ ; / at for mod remainder rem <an exponent (**)> group
having intersect minus order start union where connect ||
multiset
行:6
文本:WHERE ITMREF_0 =MT AND STA_0 IN ('A1','A2','A3') GROUP BY ITMREF_0)-
CREATE OR REPLACE FUNCTION GJERP_MUCOUNT(MT IN VARCHAR2) RETURN NUMBER IS
RENUM NUMBER;
BEGIN
SELECT SUM(QTYSTU_0) INTO RENUM
FROM STOCK
WHERE ITMREF_0 =MT AND STA_0 IN ('A1','A2','A3') GROUP BY ITMREF_0)-
(SELECT SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0)
FROM MFGOPE INNER JOIN MFGMAT
ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0
WHERE MFGMAT.MATSTA_0< =2 AND MFGOPE.XMMSTA_0=2 AND MFGMAT.ITMREF_0=MT
GROUP BY MFGMAT.ITMREF_0);
RETURN(RENUM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END
PS:程序块中的SELECT 必跟INTO~