大家好,我写了个存储过程,其中有这么一句:
update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);其中YF_YFKC是个表,zjy是个试图
我把这个语句,在SQL窗口执行都没问题,一切正常。
但写到存储过程里,一编译,就出现错误提示:文本:update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);错误:PLS-00103: 出现符号 "WHERE"在需要下列之一时:
;returnreturningandor
行:6
文本:update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);错误: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>请问是怎么回事,应该怎么写,才能实现我的功能,而不出现错误,
update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);其中YF_YFKC是个表,zjy是个试图
我把这个语句,在SQL窗口执行都没问题,一切正常。
但写到存储过程里,一编译,就出现错误提示:文本:update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);错误:PLS-00103: 出现符号 "WHERE"在需要下列之一时:
;returnreturningandor
行:6
文本:update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);错误: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>请问是怎么回事,应该怎么写,才能实现我的功能,而不出现错误,
CREATE OR REPLACE PROCEDURE cxjs ISBEGIN
update yf_yfkc set sl=0;
commit;
update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh);
update yf_yfkc a set sl=sl-(select sl from zqy where zqy.ypbm=a.ypbm and zqy.xh=a.xh) where exists (select 1 from zqy where zqy.ypbm=a.ypbm and zqy.xh=a.xh);
update yf_yfkc a set sl=sl-(select sl from zsh where zsh.ypbm=a.ypbm and zsh.xh=a.xh) where exists (select 1 from zsh where zsh.ypbm=a.ypbm and zsh.xh=a.xh);
commit;
delete from yy_ypbm_ls;
insert into yy_ypbm_ls select * from yf_yfkc;
update yy_ypbm_ls a set sl=sl-(select sl from wqy where wqy.ypbm=a.ypbm and wqy.xh=a.xh) where exists (select 1 from wyq where wqy.ypbm=a.ypbm and wqy.xh=a.xh);
commit;END;
procedure中试试替换成这句,后面两句也是
execute immediate 'update yf_yfkc a set sl=sl+(select sl from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh) where exists (select 1 from zjy where zjy.ypbm=a.ypbm and zjy.xh=a.xh)';
这样试试