一个简单oracle存储过程

本存储过程实现创建表和删除表，并应用到日期函数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