生產(chǎn)有一張日志表，數(shù)據(jù)量很大，需要按月進(jìn)行存儲(chǔ)，存儲(chǔ)過(guò)程如下：

CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE IStable_name1 VARCHAR2(50);create_table_sql VARCHAR2(4000);insert_data_sql VARCHAR2(4000);delete_data_sql VARCHAR2(4000); -- v_exists INT:=0;v_exists NUMBER (10, 0); BEGINSELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name1 FROM DUAL;select count(1) into v_exists from user_tables where table_name=UPPER(table_name1);--dbms_output.put_line(sname);--dbms_output.put_line(table_name1);if (v_exists <1)then-- dbms_output.put_line(sname);create_table_sql := 'create table ' || table_name1 || ' (autudt TIMESTAMP(6),authentype VARCHAR2(2000),userid VARCHAR2(2000),orgid VARCHAR2(2000),org2id VARCHAR2(2000),realname VARCHAR2(2000),success VARCHAR2(2000),idpname VARCHAR2(2000),idpip VARCHAR2(2000),vistorip VARCHAR2(2000),vistorbrowser VARCHAR2(2000),spid VARCHAR2(2000),spurl VARCHAR2(2000),info VARCHAR2(2000),autdesc VARCHAR2(2000),taketime VARCHAR2(2000),orgnamefullpath VARCHAR2(2000),ines INTEGER default 0,logid VARCHAR2(32),inputaccount VARCHAR2(2000),channel NUMBER(32),pushstate NUMBER(2) default 0,appid VARCHAR2(50))';EXECUTE IMMEDIATE create_table_sql;commit;end if;--將FATHER_TABLE表中取上月記錄 添加到新創(chuàng)建的分表中。insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) ';EXECUTE IMMEDIATE insert_data_sql;--刪除FATHER_TABLE表中時(shí)間在上個(gè)月范圍內(nèi)的所有數(shù)據(jù)delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))';EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN-- ROLLBACK; END NEWLOG4_SUB_TABLE; create or replace procedure pro_exelog2 is datetime INTEGER :=30; --Storage duration v_name varchar2(40); v_lastmouth varchar2(40); v_count number:=0; v_tbrecordname varchar2(10) :='newlog2_'; v_table varchar2(4000); v_exists INT:=0; beginselect tb_name,v_tbrecordname||to_char(sysdate-datetime,'yyyymm') into v_name,v_lastmouth from tb_record where type =2 and status=1;select count(1) into v_count from tb_record where type =2 and tb_name=UPPER(v_lastmouth);if v_count=0 thenbeginupdate tb_record set status=0 where type =2; -- erase statusinsert into tb_record(type,tb_name,status) values(2,UPPER(v_lastmouth),1); --insert new table recordend;end if;select count(1) into v_exists from user_tables where table_name=UPPER(v_lastmouth);--create new table when time > 30 daysif v_exists!=1 thenbeginv_table:='create table '||v_name||' (AUTUDT TIMESTAMP(6),AUTHENTYPE VARCHAR2(2000),USERID VARCHAR2(2000),ORGID VARCHAR2(800),ORG2ID VARCHAR2(800),REALNAME VARCHAR2(800),SUCCESS VARCHAR2(20),IDPNAME VARCHAR2(2000),IDPIP VARCHAR2(2000),VISTORIP VARCHAR2(2000),VISTORBROWSER VARCHAR2(2000),SPID VARCHAR2(2000),SPURL VARCHAR2(2000),INFO VARCHAR2(2000),AUTDESC VARCHAR2(2000),TAKETIME VARCHAR2(2000),ORGNAMEFULLPATH VARCHAR2(2000))';EXECUTE IMMEDIATE v_table;commit;end;end if;v_table:='insert into '||v_lastmouth||' select * from newlog2 where autudt <(sysdate-'||datetime||')'; --insert new data from old tableEXECUTE IMMEDIATE v_table;commit;delete from newlog2 where autudt <(sysdate-datetime); --delete old datacommit; end pro_exelog2; create or replace procedure pro_exelog1 is datetime INTEGER :=30; v_name varchar2(40); v_lastmouth varchar2(40); v_count int:=0; v_table varchar2(4000); v_exists INT:=0; v_tbrecordname varchar2(10) :='newlog1_'; beginselect tb_name ,v_tbrecordname||to_char(sysdate-datetime,'yyyymm') into v_name,v_lastmouth from tb_record where type =1 and status=1;select count(1) into v_count from tb_record where type =1 and tb_name=UPPER(v_lastmouth);if v_count=0 thenbeginupdate tb_record set status=0 where type=1; -- erase statusinsert into tb_record(type,tb_name,status) values(1,UPPER(v_lastmouth),1); --insert new table recordend;end if;select count(1) into v_exists from user_tables where table_name=UPPER(v_lastmouth);--create new table when time > 30 daysif v_exists!=1 thenbeginv_table:='create table '||v_lastmouth||' (OPDT TIMESTAMP(6),OPUSERID VARCHAR2(32),OPUSERIP VARCHAR2(20),OPTYPE VARCHAR2(20),MAINOBJECTID VARCHAR2(32), MAINOBJECTTYPE VARCHAR2(20),MAINACTION VARCHAR2(50),BEFOREACTIONOBJECTJSONSTRING CLOB,AFTERACTIONOBJECTJSONSTRING CLOB,EXCCUTESTATUS VARCHAR2(2000),ERRORDESC VARCHAR2(2000),MAINOBJECTORG VARCHAR2(32),LOG_ID VARCHAR2(32) not null,PLATFORM VARCHAR2(20))';EXECUTE IMMEDIATE v_table;commit;end;end if;v_table:='insert into '||v_lastmouth||' select * from newlog1 where opdt <(sysdate-'||datetime||')'; --insert new data from old tableEXECUTE IMMEDIATE v_table;commit;delete from newlog1 where opdt <(sysdate-datetime); --delete old datacommit; end pro_exelog1; create or replace PROCEDURE PROC_CREATE_SUB_TABLE IStable_name VARCHAR2(50);create_table_cursor NUMBER(10);create_table_sql VARCHAR2(1000);insert_data_sql VARCHAR2(1000);delete_data_sql VARCHAR2(1000);v_exists INT:=0; BEGIN--生成分表的表名。SELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name FROM DUAL;select count(1) into v_exists from user_tables where table_name=UPPER(table_name);if v_exists!=1 then create_table_cursor := DBMS_SQL.OPEN_CURSOR;--打開(kāi)游標(biāo)--拼出創(chuàng)建表的SQL語(yǔ)句，并執(zhí)行。create_table_sql := 'create table ' || table_name || ' (autudt TIMESTAMP(6),authentype VARCHAR2(2000),userid VARCHAR2(2000),orgid VARCHAR2(2000),org2id VARCHAR2(2000),realname VARCHAR2(2000),success VARCHAR2(2000),idpname VARCHAR2(2000),idpip VARCHAR2(2000),vistorip VARCHAR2(2000),vistorbrowser VARCHAR2(2000),spid VARCHAR2(2000),spurl VARCHAR2(2000),info VARCHAR2(2000),autdesc VARCHAR2(2000),taketime VARCHAR2(2000),orgnamefullpath VARCHAR2(2000),ines INTEGER default 0,logid VARCHAR2(32),inputaccount VARCHAR2(2000),channel NUMBER(32),pushstate NUMBER(2) default 0)';DBMS_SQL.PARSE(create_table_cursor, create_table_sql, DBMS_SQL.V7);DBMS_SQL.CLOSE_CURSOR(create_table_cursor);end if;--將FATHER_TABLE表中取上月記錄 添加到新創(chuàng)建的分表中。insert_data_sql := 'INSERT INTO ' || table_name || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp + NumToYMInterval(-1, 'MONTH')) ';v_table:='insert into '||v_lastmouth||' select * from newlog2 where autudt <(sysdate-'||datetime||')';EXECUTE IMMEDIATE insert_data_sql;--刪除FATHER_TABLE表中時(shí)間在上個(gè)月范圍內(nèi)的所有數(shù)據(jù)delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp + NumToYMInterval(-1, 'MONTH')) ';EXECUTE IMMEDIATE delete_data_sql;COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK; END PROC_CREATE_SUB_TABLE;

?

?

?

?

總結(jié)

以上是生活随笔為你收集整理的按月拆分数据库表--oracle的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。