--你的存储过程是怎么样执行这个语句的 --你按照下面的方式试试 create or replace procedure testpro as sqlstr varchar2(1000); begin sqlstr:='create table TEMP as select c.DWMC,count(distinct(b.YGBH)) NUM from SJXX a,YGXX b,ZD_DW c where b.DW=c.DWDM and (b.SJRQ between ''20101001'' and ''20101031235959'') and a.SJLB in (''010000'', ''010001'', ''010002'', ''010003'', ''010004'') and a.SJBH=b.SJBH group by DWMC'; execute immediate sqlstr; end;
你确认用sqlplus和toad连接的是同一个数据库?
在过程里面dml需要动态str:='select c.DWMC,count(distinct(b.YGBH)) NUM from SJXX a,YGXX b,ZD_DW c where b.DW=c.DWDM and (b.SJRQ between ''20101001'' and ''20101031235959'') and a.SJLB in (''010000'', ''010001'', ''010002'', ''010003'', ''010004'') and a.SJBH=b.SJBH group by DWMC'
--你按照下面的方式试试
create or replace procedure testpro
as
sqlstr varchar2(1000);
begin
sqlstr:='create table TEMP as
select c.DWMC,count(distinct(b.YGBH)) NUM
from SJXX a,YGXX b,ZD_DW c
where b.DW=c.DWDM
and (b.SJRQ between ''20101001'' and ''20101031235959'')
and a.SJLB in (''010000'', ''010001'', ''010002'', ''010003'', ''010004'')
and a.SJBH=b.SJBH
group by DWMC';
execute immediate sqlstr;
end;
from SJXX a,YGXX b,ZD_DW c
where b.DW=c.DWDM
and (b.SJRQ between ''20101001'' and ''20101031235959'')
and a.SJLB in (''010000'', ''010001'', ''010002'', ''010003'', ''010004'')
and a.SJBH=b.SJBH
group by DWMC'