一条sql语句单独能执行在程储过程中却不行,哪位帮我看看
过程如下:
CREATE OR REPLACE PROCEDURE CALC_PRO_HEAT(CALC_TIME IN VARCHAR2) IS
/*计算calc_time月份的煤气GJ数,并更新到产品分配表ALLOT */
BEGIN
UPDATE ALLOT T
SET T.PRODUCT_HEAT = NVL((SELECT CALC_NUM * T.ALLOT_NUM
FROM VL_USERHEAT K
WHERE T.USER_ID = K.USER_ID AND
T.PDT_ID = K.PDT_ID AND
K.CALC_TIME = T.ALLOT_TIME),
0)
WHERE T.ALLOT_TIME = CALC_TIME;
IF SQL%FOUND THEN
COMMIT;
ELSE
NULL;
END IF;
END CALC_PRO_HEAT;
每次都是这个语句不行
UPDATE ALLOT T
SET T.PRODUCT_HEAT = NVL((SELECT CALC_NUM * T.ALLOT_NUM
FROM VL_USERHEAT K
WHERE T.USER_ID = K.USER_ID AND
T.PDT_ID = K.PDT_ID AND
K.CALC_TIME = T.ALLOT_TIME),
0)
WHERE T.ALLOT_TIME = CALC_TIME;
但是单独执行是可以的
到了程储过程中就会出现:
Compilation errors for PROCEDURE DLCREP.CALC_PRO_HEAT
Error: PLS-00103: 出现符号 "SELECT"在需要下列之一时:
(-+modnotnullothers
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentexistsmaxminpriorsqlstddev
sumvarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>
Line: 5
Text: SET T.PRODUCT_HEAT = NVL((SELECT CALC_NUM * T.ALLOT_NUM
Error: PLS-00103: 出现符号 ","在需要下列之一时:
;returnreturningandor
Line: 9
Text: K.CALC_TIME = T.ALLOT_TIME),
Error: PLS-00103: 出现符号 "IF"
Line: 12
Text: IF SQL%FOUND THEN
Error: PLS-00103: 出现符号 "END"
Line: 17
Text: END CALC_PRO_HEAT;
哪位能帮我解决一下,数据库是8i的,在9i中执行是没有错误的