存储过程动态执行sql提示ORA-14552 在查询或 DML 中无法执行 DDL

[RUA好多鱼~]

项目中有个统计需要针对一个多行两列的查询结果进行计算 我这里是想用存储过程 把查询结果作为一个表 然后对这个表进行操作
但是在建立这个表的时候报了14552这个错 下面是我的存储过程
create or replace procedure MOVE_AVERAGE is
v_data number;
begin
select COUNT(*) into v_data from user_tables where table_name='DATA_TEMP';
IF v_data>0 THEN
execute immediate 'DROP TABLE DATA_TEMP';
END IF;
execute immediate 'create table DATA_TEMP as (select ROWNUM rn,X.Week,X.num+Y.num result from (SELECT A.week,count(B.patient) num FROM (select level week from dual connect by level<=52) A left join (SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date((to_char(sysdate,''yyyy'')-2)||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date((to_char(sysdate,''yyyy'')-2)||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) X,(SELECT A.week,count(B.patient) num FROM (select level week from dual connect by level<=52) A left join (SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date(to_char(sysdate,''yyyy'')||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date(to_char(sysdate,''yyyy'')||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) Y WHERE X.week=Y.week)';
end MOVE_AVERAGE;
因为这个统计只能用oracle来处理数据不能经过java后台所以 我通过一个函数来调用 函数下面这个：
create or replace function CAL_MOVE_AVERAGE return varchar2 is
begin
MOVE_AVERAGE();
return 'DATA_TEMP';
end CAL_MOVE_AVERAGE;
然后我调用函数SELECT CAL_MOVE_AVERAGE() FROM DUAL的时候就报了这个不能再查询或DML中执行DDL的错误
请问这个是怎么回事，对数据库这方面不太懂，以前都是用java来处理数据，请大神们指点,第一次发帖也不知道板块对不对

[ghx287524027]

execute immediate 后面只能是 DML 操作。你为什么每次要 drop table呢，直接delete掉数据，然后执行 execute immediate insert不行吗？

[RUA好多鱼~]

没写过存储过程，感觉直接create table as这样写起来简单，但是DROP这里执行的时候没错啊，我这个报错是在if循环 下面 那个execute immediate create table那里报的错，不知道是哪里出了问题

[RUA好多鱼~]

你是说，create 这样的语句是不能用这种动态执行的么，那drop这里为什么不会报错

[卖水果的net]

execute immediate 'create table 把这个建表语句，打印出来，在 plsql 中执行一下，看看有什么问题； 如果你们看不出来有什么问题，可以把语句贴上来；

[RUA好多鱼~]

我在pl、sql里单独执行了一下那个建表语句，没问题啊，成功了

[RUA好多鱼~]

哦哦 应该是2楼大哥说的问题 现在drop也报错，我去改一下那两个sql试试

[js14982]

你的存储过程没问题，是函数或者函数调用的问题吧。

[js14982]

把drop table和create table改成delete和insert into试试吧，提示的是不能用DDL语句。 DDL：drop ，alter，create，truncate... DML：insert into ，delete，update...

[RUA好多鱼~]

啊？没问题啊，函数里也没写啥，只是用函数来调用它而已，难道用函数调用存储过程会有这样的问题？我正在按2楼大哥说的尝试一下，还没改完，改一改试试

[RUA好多鱼~]

恩恩 我正在改 多谢指点

[RUA好多鱼~]

create or replace procedure MOVE_AVERAGE is begin execute immediate 'delete from DATA_TEMP'; execute immediate 'insert into DATA_TEMP select X.Week,X.num+Y.num result from (SELECT A.week,count(B.patient) num FROM (select level week from dual connect by level<=52) A left join (SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date((to_char(sysdate,''yyyy'')-2)||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date((to_char(sysdate,''yyyy'')-2)||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) X,(SELECT A.week,count(B.patient) num FROM (select level week from dual connect by level<=52) A left join (SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date(to_char(sysdate,''yyyy'')||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date(to_char(sysdate,''yyyy'')||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) Y WHERE X.week=Y.week)'; end MOVE_AVERAGE; 我改成dml操作的了，在delete语句那就报了14551，无法再查询中执行DML操作，

[js14982]

我觉得不能这么玩，想其他办法吧，java中只支持调用oracle查询么？

[RUA好多鱼~]

关键是不能走后台，要直接在数据库中得到查询结果，我们现在是用一个叫人大金仓的平台做一个周报，周报里有一个统计表是这样的，但是那个平台是直连数据库的，不能走我们自己的后台，所以没办法，走后台的话就很容易了

[RUA好多鱼~]

create or replace procedure MOVE_AVERAGE is begin execute immediate 'delete from DATA_TEMP'; commit; execute immediate 'insert into DATA_TEMP (select ROWNUM rn,X.Week,X.num+Y.num result from ( SELECT A.week,count(B.patient) num FROM ( select level week from dual connect by level<=52) A left join ( SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date((to_char(sysdate,''yyyy'')-2)||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date((to_char(sysdate,''yyyy'')-2)||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) X, ( SELECT A.week,count(B.patient) num FROM ( select level week from dual connect by level<=52) A left join ( SELECT e.patient_name patient,to_char(e.add_time,''iw'') week from cydes_epiinv_hfm e where (e.delete_status<>''1'' or e.delete_status is null) and e.add_time>=to_date(to_char(sysdate,''yyyy'')||''-01-01'',''yyyy-MM-dd'') and e.add_time<=to_date(to_char(sysdate,''yyyy'')||''-12-31'',''yyyy-MM-dd'')) B on A.Week=B.week group by A.week order by A.week) Y WHERE X.week=Y.week)'; commit; end MOVE_AVERAGE; 这个是修改后的存储过程，经过楼上大神的指点已经可以执行了，我的调用存储过程的方式不对，不应该作为一个函数去调用 这是调用方法 begin MOVE_AVERAGE; end; 第一次发帖，问了点小白的问题，感谢大神们的热心指点