本存储过程实现创建表和删除表，并应用到日期函数sysdate,add_months,
PL/SQL Devoleper 8.0开发
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;
COMMIT;
END;
遇到权限问题参考此文解决http://www.itpub.net/viewthread.php?tid=413853&extra=&page=1