tempsql varchar2(4000); cSeq varchar2(10); dykm_yh varchar2(10); dykm_yfzk varchar2(10); dykm_qtyzk varchar2(10); dykm_wltb varchar2(10); begin tempsql :=' DECLARE vLsh number; vJe number; vJeW4 number; vJeW5 number; vYbje number; vBwbje number; CURSOR cur_test IS select id_lsh,w_bwbje from tempw4'||cSeq||' order by w_bwbje desc; BEGIN select sum(w_bwbje) into vjeW4 from tempw4'||cSeq||'; select sum(w_bwbje) into vjeW5 from tempw5'||cSeq||'; vjeW4:=vjeW4-vJeW5; OPEN cur_test; FETCH cur_test into vLsh,vJe; WHILE cur_test%FOUND LOOP exit when vJe>=vjeW4; update tempw4'||cSeq||' set w_kmmx='''||dykm_yh||''',w_dfkm='''||dykm_wltb||''' where id_lsh=vLsh; vjeW4:=vjeW4-vJe; FETCH cur_test into vLsh,vJe; END LOOP; CLOSE cur_test; select w_ybje,w_bwbje into vYbje,vBwbje from tempw4'||cSeq||' where id_lsh=vLsh; update tempw4'||cSeq||' set w_ybje=w_ybje*(vjeW4/w_bwbje),w_bwbje=vjeW4 where id_lsh=vLsh; insert into tempw4'||cSeq||'(id_lsh,w_mxjlh,j_srr,w_zll,w_bb,w_hl,w_ybje,w_bwbje,w_sjyb,w_sjbwb,' || mxfields ||',w_dfkm) select s_t_wh_sfmx.nextval,w_mxjlh,j_srr,w_zll,w_bb,w_hl,vYbje-w_ybje,vBwbje-w_bwbje,vYbje-w_ybje,vBwbje-w_bwbje,' || mxfields ||',w_dfkm from tempw4'||cSeq||' where id_lsh=vLsh; END; '; end EXECUTE IMMEDIATE tempsql; ********************************************在tempsql里已经有delcare .. begin .. end,并且用 DBMS_OUTPUT.PUT_LINE把tempsql打印出来在worksheet里可以正常运行。
EXECUTE IMMEDIATE tempsql;这两条命令如果你写在一个存储过程里,是不是也要遵照存储过程的标准写法,declare ... begin ... end 来做呢?
疑问?没见过这种写法。
看提示的意思是这句不能放在Begin/Declare……的前面吧?
若可以的话,后面是不是需要加';'号?
cSeq varchar2(10);
dykm_yh varchar2(10);
dykm_yfzk varchar2(10);
dykm_qtyzk varchar2(10);
dykm_wltb varchar2(10);
begin
tempsql :='
DECLARE
vLsh number;
vJe number;
vJeW4 number;
vJeW5 number;
vYbje number;
vBwbje number;
CURSOR cur_test IS
select id_lsh,w_bwbje from tempw4'||cSeq||' order by w_bwbje desc;
BEGIN
select sum(w_bwbje) into vjeW4 from tempw4'||cSeq||';
select sum(w_bwbje) into vjeW5 from tempw5'||cSeq||';
vjeW4:=vjeW4-vJeW5;
OPEN cur_test;
FETCH cur_test into vLsh,vJe;
WHILE cur_test%FOUND
LOOP
exit when vJe>=vjeW4;
update tempw4'||cSeq||' set w_kmmx='''||dykm_yh||''',w_dfkm='''||dykm_wltb||''' where id_lsh=vLsh;
vjeW4:=vjeW4-vJe;
FETCH cur_test into vLsh,vJe;
END LOOP;
CLOSE cur_test;
select w_ybje,w_bwbje into vYbje,vBwbje from tempw4'||cSeq||' where id_lsh=vLsh;
update tempw4'||cSeq||' set w_ybje=w_ybje*(vjeW4/w_bwbje),w_bwbje=vjeW4 where id_lsh=vLsh;
insert into tempw4'||cSeq||'(id_lsh,w_mxjlh,j_srr,w_zll,w_bb,w_hl,w_ybje,w_bwbje,w_sjyb,w_sjbwb,' || mxfields ||',w_dfkm)
select s_t_wh_sfmx.nextval,w_mxjlh,j_srr,w_zll,w_bb,w_hl,vYbje-w_ybje,vBwbje-w_bwbje,vYbje-w_ybje,vBwbje-w_bwbje,' || mxfields ||',w_dfkm from tempw4'||cSeq||' where id_lsh=vLsh;
END;
';
end
EXECUTE IMMEDIATE tempsql;
********************************************在tempsql里已经有delcare .. begin .. end,并且用
DBMS_OUTPUT.PUT_LINE把tempsql打印出来在worksheet里可以正常运行。
我看着有点困难,不过我建议你使用native sql,执行速度也快,实现动态sql简单明了,用dbms sql 有些烦。
select s_t_wh_sfmx.nextval,w_mxjlh,j_srr,w_zll,w_bb,w_hl,vYbje-w_ybje,vBwbje-w_bwbje,vYbje-w_ybje,vBwbje-w_bwbje,' || mxfields ||',w_dfkm from tempw4'||cSeq||' where id_lsh=vLsh;
END;';上面的语句多了一个END.还有tempsql在那走出来?
***********************************************
PLS-00103: 出现符号 ""在需要下列之一时:
begindeclareexitforfunction
gotoifloopmodnullpackagepragmaprocedureraisereturnselect
separatetypeupdatewhilewith<an identifier>
<a double-quoted delimited-identifier><a bind variable><<
formtablecallclosecurrentdefinedeletefetchlockinsertopen
rollbacksavepointsetsqlexecutecommitforalllibraryOPERATOR_
<a single-quoted SQL string>
***********************************************
不知为什么???
cSeq varchar2(10);
dykm_yh varchar2(10);
dykm_yfzk varchar2(10);
dykm_qtyzk varchar2(10);
dykm_wltb varchar2(10);sorry,在最上面要多加一个declare.其它的,再想想.