create or replace procedure P_CREATE_HISTORY_TABLE is V_SQL String(31000) :='';
begin FOR i in 1..2 LOOP declare num number; begin select count(1) into num from user_tables where table_name = 'onoffhist_'||to_char(add_months(sysdate,i),'YYYYMM'); if num >0 then execute immediate 'drop table onoffhist_'||to_char(add_months(sysdate,i),'YYYYMM'); end if; end; V_SQL := V_SQL||' create table dtpgsis_onoff.onoffhist_'||to_char(add_months(sysdate,i),'YYYYMM') ||'( id number(10,0) not null enable, sn number(*,0), datetime varchar2(20), maxvalue float(126), minvalue float(126), value float(126), constraint onoffhist_'||to_char(add_months(sysdate,i),'YYYYMM')||' primary key(id) using index pctfree 10 initrans 2 maxtrans 255 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DTPGSIS_ONOFF" ENABLE )' ; -- DBMS_OUTPUT.put_line(V_SQL); EXECUTE IMMEDIATE V_SQL; END LOOP;