v_String1:='insert TEMP_QTCNFX(ny,avg_cnl,avg_Dnwnl,avg_sjnl,avg_shlv,avg_shl,avg_rzl,avg_dbl,
avg_rtl,avg_gwz,avg_txbs,avg_zxjs,avg_Mrns,avg_mrnrc,avg_cmns,avg_cmnrc)
select '||i_nf||'||''年''||to_char('||t_i||')||''月'', avg(cnl) * '||t_datecount||' as avg_cnl,
avg(Dnwnl) * '||t_datecount||' as avg_dnwnl,avg(sjnl) * '||t_datecount||' as avg_sjnl,
avg(shlv) as avg_shlv,avg(shl) * '||t_datecount||' as avg_shl,
avg(rzlv) as avg_rzl,
avg(rdblv) as avg_dbl, avg(rtlv) as avg_rtlv, avg(gwz) as avg_gwz,
avg(txbs) as avg_txbs, avg(zxjs) as avg_zxjs ,
avg(mrns) as avg_mrns,avg(mrnrc) as avg_mrnrc,
avg(cmns) as avg_cmns,avg(cmnrc) as avg_cmnrc
from CNGL_QTCNDJ
where to_char(clrq,''yyyy-mm-dd'') >= '||t_clrq_q||' and to_char(clrq,''yyyy-mm-dd'') < '||t_clrq_z||'
and fscbh = '||i_ncbh||'';
Execute Immediate v_String1;
这条语句在 pL/sql 中执行没问题,怎么放到存储过程中就报ORA-00933: SQL 命令未正确结束!
请指教!
avg_rtl,avg_gwz,avg_txbs,avg_zxjs,avg_Mrns,avg_mrnrc,avg_cmns,avg_cmnrc)
select '||i_nf||'||''年''||to_char('||t_i||')||''月'', avg(cnl) * '||t_datecount||' as avg_cnl,
avg(Dnwnl) * '||t_datecount||' as avg_dnwnl,avg(sjnl) * '||t_datecount||' as avg_sjnl,
avg(shlv) as avg_shlv,avg(shl) * '||t_datecount||' as avg_shl,
avg(rzlv) as avg_rzl,
avg(rdblv) as avg_dbl, avg(rtlv) as avg_rtlv, avg(gwz) as avg_gwz,
avg(txbs) as avg_txbs, avg(zxjs) as avg_zxjs ,
avg(mrns) as avg_mrns,avg(mrnrc) as avg_mrnrc,
avg(cmns) as avg_cmns,avg(cmnrc) as avg_cmnrc
from CNGL_QTCNDJ
where to_char(clrq,''yyyy-mm-dd'') >= '||t_clrq_q||' and to_char(clrq,''yyyy-mm-dd'') < '||t_clrq_z||'
and fscbh = '||i_ncbh||'';
Execute Immediate v_String1;
这条语句在 pL/sql 中执行没问题,怎么放到存储过程中就报ORA-00933: SQL 命令未正确结束!
请指教!
avg_rtl,avg_gwz,avg_txbs,avg_zxjs,avg_Mrns,avg_mrnrc,avg_cmns,avg_cmnrc)
select '||i_nf||'||''年''||to_char('||t_i||')||''月'', avg(cnl) * '||t_datecount||' as avg_cnl,
avg(Dnwnl) * '||t_datecount||' as avg_dnwnl,avg(sjnl) * '||t_datecount||' as avg_sjnl,
avg(shlv) as avg_shlv,avg(shl) * '||t_datecount||' as avg_shl,
avg(rzlv) as avg_rzl,
avg(rdblv) as avg_dbl, avg(rtlv) as avg_rtlv, avg(gwz) as avg_gwz,
avg(txbs) as avg_txbs, avg(zxjs) as avg_zxjs ,
avg(mrns) as avg_mrns,avg(mrnrc) as avg_mrnrc,
avg(cmns) as avg_cmns,avg(cmnrc) as avg_cmnrc
from CNGL_QTCNDJ
where to_char(clrq,''yyyy-mm-dd'') >= '||t_clrq_q||' and to_char(clrq,''yyyy-mm-dd'') < '||t_clrq_z||'
and fscbh = '||i_ncbh||''我把这句从存储过程中摘出来,放在PL/Sql,中执行没错,可是jsp调用存储过程的时候在Execute Immediate v_String1;行 报 ORA-00933: SQL 命令未正确结束!
最好输出一下v_String1(用楼上的方法)
可能是某个地方的单引号的问题select '||i_nf||'||''年''||to_char('||t_i||')||''月''i_nf =2006,t_i=1
这出来的是
select 2006'年'1'月' 这肯定不对阿而且你的动态sql根本没起什么作用啊,一个参数也没用上
那还不如不用呢
insert into temp_qtcnfx(ny,avg_cnl,avg_Dnwnl,avg_sjnl,avg_shlv,avg_shl,avg_rzl,avg_dbl,
avg_rtl,avg_gwz,avg_txbs,avg_zxjs,avg_Mrns,avg_mrnrc,avg_cmns,avg_cmnrc)
select '2005'||'Äê'||to_char('02')||'ÔÂ', avg(cnl) * '21' as avg_cnl,
avg(Dnwnl) * '||t_datecount||' as avg_dnwnl,avg(sjnl) * '21' as avg_sjnl,
avg(shlv) as avg_shlv,avg(shl) * '21' as avg_shl,
avg(rzlv) as avg_rzl,
avg(rdblv) as avg_dbl, avg(rtlv) as avg_rtlv, avg(gwz) as avg_gwz,
avg(txbs) as avg_txbs, avg(zxjs) as avg_zxjs ,
avg(mrns) as avg_mrns,avg(mrnrc) as avg_mrnrc,
avg(cmns) as avg_cmns,avg(cmnrc) as avg_cmnrc
from CNGL_QTCNDJ
where to_char(clrq,'yyyy-mm-dd') >= '2005-12-15' and to_char(clrq,'yyyy-mm-dd') < '2005-12-15'
and fscbh = '1'
报:
ORU-10028: line length overflow limit of 255 chars per line
但是为什么单独拿出来就可以呢?
DBMS_OUTPUT.PUT_LINE(SUBSTRB(v_String1,256));
declare
v_String1 varchar2(4000);
i_nf varchar2(15);
t_i varchar(15);
t_datecount int;
t_clrq_q date;
i_ncbh varchar2(15);
t_clrq_z date;
begin
i_nf:='2005';
t_i:='02';
t_datecount:='15';
i_ncbh:='1';
v_String1:='insert into TEMP_QTCNFX(ny,avg_cnl,avg_Dnwnl,avg_sjnl,avg_shlv,avg_shl,avg_rzl,avg_dbl,
avg_rtl,avg_gwz,avg_txbs,avg_zxjs,avg_Mrns,avg_mrnrc,avg_cmns,avg_cmnrc)
select '||i_nf||'||''年''||to_char('||t_i||')||''月'', avg(cnl) * '||t_datecount||' as avg_cnl,
avg(Dnwnl) * '||t_datecount||' as avg_dnwnl,avg(sjnl) * '||t_datecount||' as avg_sjnl,
avg(shlv) as avg_shlv,avg(shl) * '||t_datecount||' as avg_shl,avg(rzlv) as avg_rzl, avg(rdblv) as avg_dbl,
avg(rtlv) as avg_rtlv, avg(gwz) as avg_gwz,avg(txbs) as avg_txbs, avg(zxjs) as avg_zxjs ,
avg(mrns) as avg_mrns,avg(mrnrc) as avg_mrnrc,avg(cmns) as avg_cmns,avg(cmnrc) as avg_cmnrc from CNGL_QTCNDJ
where to_char(clrq,''yyyy-mm-dd'') >= ''2005-11-15'' and to_char(clrq,''yyyy-mm-dd'') < ''2006-12-12''
and fscbh = '||i_ncbh||'';
--dbms_output.put_line(v_String1);
Execute Immediate v_String1;
-- Execute immediate 'select * from TEMP_QTCNFX';
end;
没有问题!