create or replace procedure pm_proc is
province_id number(2):=11; --需要修改为本省ID
last_end_flag number;
prop_date_begin date;
prop_date_end date;
sysdate_ date;
sql_ varchar2(1024);
sql_init varchar2(1024);
sql_condition varchar2(1024);
begin
select sysdate into sysdate_ from dual;
--检查上次执行是否结束
select count(*) into last_end_flag from prop_log p where p.start_flag = 1and end_flag = 0;
if last_end_flag > 0 then
insert into PROP_LOG values (1, 0, sysdate_, sysdate, 0, '存储过程上次执行未结束');
commit;
return;
end if;
--本次执行开始,首先向 prop_log 表中添加启动记录
insert into PROP_LOG values (1, 0, sysdate_, sysdate_, 0, '');
commit; --每次取 sysdate - 7分钟 到 sysdate - 2分钟 之间的数据放入中间表中。
--本次执行采集数据的开始时间
select sysdate - 1 / 24 / 60 * 7 into prop_date_begin from dual;
--本次采集数据的结束时间
select sysdate - 1 / 24 / 60 * 2 into prop_date_end from dual;
--判断是否跨天
if to_char(prop_date_begin, 'yyyymmdd') = to_char(prop_date_end, 'yyyymmdd') then
--这行开始报错求解
create table propertyvalues_pm_up as (select * from propertyvalues p ); --where p.datestr = to_char(prop_date_begin, 'yyyymmdd') and p.timestr > to_char(prop_date_begin, 'hh24miss') and p.timestr < to_char(prop_date_end, 'hh24miss'));
commit;
else
create table propertyvalues_pm_up as (select p.*,sysdate_ as SYSDATE_FLAG from propertyvalues p where (p.datestr = to_char(prop_date_begin, 'yyyymmdd') and p.timestr > to_char(prop_date_begin, 'hh24miss')) or (p.datestr = to_char(prop_date_end, 'yyyymmdd') and p.timestr < to_char(prop_date_end, 'hh24miss')));
commit;
end if;
province_id number(2):=11; --需要修改为本省ID
last_end_flag number;
prop_date_begin date;
prop_date_end date;
sysdate_ date;
sql_ varchar2(1024);
sql_init varchar2(1024);
sql_condition varchar2(1024);
begin
select sysdate into sysdate_ from dual;
--检查上次执行是否结束
select count(*) into last_end_flag from prop_log p where p.start_flag = 1and end_flag = 0;
if last_end_flag > 0 then
insert into PROP_LOG values (1, 0, sysdate_, sysdate, 0, '存储过程上次执行未结束');
commit;
return;
end if;
--本次执行开始,首先向 prop_log 表中添加启动记录
insert into PROP_LOG values (1, 0, sysdate_, sysdate_, 0, '');
commit; --每次取 sysdate - 7分钟 到 sysdate - 2分钟 之间的数据放入中间表中。
--本次执行采集数据的开始时间
select sysdate - 1 / 24 / 60 * 7 into prop_date_begin from dual;
--本次采集数据的结束时间
select sysdate - 1 / 24 / 60 * 2 into prop_date_end from dual;
--判断是否跨天
if to_char(prop_date_begin, 'yyyymmdd') = to_char(prop_date_end, 'yyyymmdd') then
--这行开始报错求解
create table propertyvalues_pm_up as (select * from propertyvalues p ); --where p.datestr = to_char(prop_date_begin, 'yyyymmdd') and p.timestr > to_char(prop_date_begin, 'hh24miss') and p.timestr < to_char(prop_date_end, 'hh24miss'));
commit;
else
create table propertyvalues_pm_up as (select p.*,sysdate_ as SYSDATE_FLAG from propertyvalues p where (p.datestr = to_char(prop_date_begin, 'yyyymmdd') and p.timestr > to_char(prop_date_begin, 'hh24miss')) or (p.datestr = to_char(prop_date_end, 'yyyymmdd') and p.timestr < to_char(prop_date_end, 'hh24miss')));
commit;
end if;
想要执行就用execute immediate 'create table .....';执行。
可参考
1、8i以下版本可以用dbms_sql包动态执行ddl
2、8i以上版本还可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql'); 如:
declare
cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE (cur, 'create table a(a number(2))', DBMS_SQL.V7);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
END;