存储过程动态执行sql提示ORA-14552 在查询或 DML 中无法执行 DDL
项目中有个统计需要针对一个多行两列的查询结果进行计算 我这里是想用存储过程 把查询结果作为一个表 然后对这个表进行操作
但是在建立这个表的时候报了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来处理数据,请大神们指点,第一次发帖也不知道板块对不对