我有一题作业,苦思好久没有结果,急!!!!!
需求如下:日志系统,每个月都有一张历史表来存储该月的日志信息,而当月信息存储在当前表中。比如,现在是11月,11月的信息存储在当前表log_data中,10月的信息存储在历史表log_data_200910中,9月的信息存储在历史表log_data_200909中,……,以此类推。这里的当前表和历史表的结构都是相同的,如下:
create table LOG_DATA (
LOG_ID VARCHAR2(16) not null,
LOG_TIME DATE not null,
APP_ID VARCHAR2(20),
MODULE_ID VARCHAR2(50),
LOG_TYPE VARCHAR2(1) not null,
LOG_CLASS VARCHAR2(1024),
LOG_LEVEL VARCHAR2(10) not null,
USER_ID VARCHAR2(30) not null,
USER_IP VARCHAR2(15),
URL VARCHAR2(255),
MESSAGE VARCHAR2(1000) not null,
constraint PK_LOG_DATA primary key (LOG_ID) )
这里的log_id是从sequence中取出来的,作为主键。
存储过程的功能描述:
1、 每个月都要判断下个月的历史表(如log_data_200912)是否存在,如果存在则DBMS_OUTPUT.PUT_LINE('log_data_200912已经存在!')否则创建下个月的历史表(如log_data_200912)。
2、然后从当前表中查询上个月的信息(必须要判断select的数据在要插入的历史表中没有重复,否则插不进去),将其插入到上个月的历史表中。(即如果到12月,要把当前表log_data的11月份日志数据insert到历史表log_data_200911中,如果log_id在历史表log_data_200911中存在,则不insert。)然后在当前表log_data中删除已经存在于历史表(如log_data_200911)中的数据,其中包括已经insert成功的和历史表中已经存在的。
3、这个存储过程需要每天晚上00:00:00执行。
谢谢!!!!!!!
需求如下:日志系统,每个月都有一张历史表来存储该月的日志信息,而当月信息存储在当前表中。比如,现在是11月,11月的信息存储在当前表log_data中,10月的信息存储在历史表log_data_200910中,9月的信息存储在历史表log_data_200909中,……,以此类推。这里的当前表和历史表的结构都是相同的,如下:
create table LOG_DATA (
LOG_ID VARCHAR2(16) not null,
LOG_TIME DATE not null,
APP_ID VARCHAR2(20),
MODULE_ID VARCHAR2(50),
LOG_TYPE VARCHAR2(1) not null,
LOG_CLASS VARCHAR2(1024),
LOG_LEVEL VARCHAR2(10) not null,
USER_ID VARCHAR2(30) not null,
USER_IP VARCHAR2(15),
URL VARCHAR2(255),
MESSAGE VARCHAR2(1000) not null,
constraint PK_LOG_DATA primary key (LOG_ID) )
这里的log_id是从sequence中取出来的,作为主键。
存储过程的功能描述:
1、 每个月都要判断下个月的历史表(如log_data_200912)是否存在,如果存在则DBMS_OUTPUT.PUT_LINE('log_data_200912已经存在!')否则创建下个月的历史表(如log_data_200912)。
2、然后从当前表中查询上个月的信息(必须要判断select的数据在要插入的历史表中没有重复,否则插不进去),将其插入到上个月的历史表中。(即如果到12月,要把当前表log_data的11月份日志数据insert到历史表log_data_200911中,如果log_id在历史表log_data_200911中存在,则不insert。)然后在当前表log_data中删除已经存在于历史表(如log_data_200911)中的数据,其中包括已经insert成功的和历史表中已经存在的。
3、这个存储过程需要每天晚上00:00:00执行。
谢谢!!!!!!!
不存在就利用 execute immediate 语句来建表。
2. 用 merge into语句来做,做完了再删。
3.建个job
is
v_count number;
v_nextmonth_tab varchar2(30);
v_curmonth_tab varchar2(30);
v_lastmonth_tab varchar2(30);
begin
v_nextmonth_tab:='LOG_DATA_'||to_char(add_months(trunc(sysdate),1),'YYYYMM');
v_lastmonth_tab:='LOG_DATA_'||to_char(add_months(trunc(sysdate),-1),'YYYYMM');
v_curmonth_tab :='LOG_DATA_'||to_char(sysdate,'YYYYMM');
select COUNT(*) into v_count from user_tables where table_name= v_nextmonth_tab;
if v_count>0 then
DBMS_OUTPUT.PUT_LINE(v_nextmonth_tab ||'已经存在!')
else
execute immediate 'create table '|| v_nextmonth_tab||' (
LOG_ID VARCHAR2(16) not null,
LOG_TIME DATE not null,
APP_ID VARCHAR2(20),
MODULE_ID VARCHAR2(50),
LOG_TYPE VARCHAR2(1) not null,
LOG_CLASS VARCHAR2(1024),
LOG_LEVEL VARCHAR2(10) not null,
USER_ID VARCHAR2(30) not null,
USER_IP VARCHAR2(15),
URL VARCHAR2(255),
MESSAGE VARCHAR2(1000) not null,
constraint PK_LOG_DATA primary key (LOG_ID) ) ';
end if;
execute immediate 'insert into ' || v_lastmonth_tab ||
' select * from ' || v_curmonth_tab || ' c where not exists(select 1 from ' || v_lastmonth_tab || ' where l where c.log_id=l.log_id)';
end;然后建立job 调用次存储过程 SYS.DBMS_JOB.SUBMIT(NJOB,
'sp_log();',
to_date(to_char(add_days(sysdate,1),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'),
'sysdate + 24/24');
COMMIT;