小弟刚刚接触存储过程不久就被安排了一个任务,不太会写,求助各位,希望大虾能给一个可以运行的例子,在此我先谢过了,如果不能写全部,那么提示我一下也好啊,谢谢各位了,使用数据库为oracle 10g。
需求如下:日志系统,每个月都有一张历史表来存储该月的日志信息,而当月信息存储在当前表中。比如,现在是11月,11月的信息存储在当前表log_data中,10月的信息存储在历史表log_data_200610中,9月的信息存储在历史表log_data_200609中,……,以此类推。这里的当前表和历史表的结构都是相同的,如下:
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_200612)是否存在,如果存在则DBMS_OUTPUT.PUT_LINE('log_data_200612已经存在!')否则创建下个月的历史表(如log_data_200612)。
2、然后从当前表中select上个月的信息(必须要判断select的数据在要插入的历史表中没有重复,否则插不进去),将其插入到上个月的历史表中。(即如果到12月,要把当前表log_data的11月份日志数据insert到历史表log_data_200611中,如果log_id在历史表log_data_200611中存在,则不insert。)然后在当前表log_data中删除已经存在于历史表(如log_data_200611)中的数据,其中包括已经insert成功的和历史表中已经存在的。
3、这个存储过程需要每天晚上00:00:00执行。
==========================================
以下是我现在的进展,估计很可笑,但我还是把它帖出来,希望大家指点,如果真的是朽木不可雕了,那就完全推倒吧!
CREATE OR REPLACE PROCEDURE transfer_log
DECLARE
v_sysdate DATE := SYSDATE;
v_year VARCHAR2(4) := TO_CHAR(v_sysdate, 'YYYY');
v_month VARCHAR2(2) := TO_CHAR(v_sysdate, 'MM');
BEGIN
v_table VARCHAR2(15):= 'LOG_DATA_' || v_year || v_month
IF EXISTS (SELECT * FROM TAB WHERE TNAME = || v_table)
THEN
DBMS_OUTPUT.PUT_LINE(v_table || ' is exists ...');
ELSE
DBMS_OUTPUT.PUT_LINE('create table ' || v_table || '...');
v_string := 'CREATE TABLE ' || v_table || ' ('
|| '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_' || v_table || ' primary key (LOG_ID)'
|| ')';
EXECUTE IMMEDIATE (v_string);
END IF
v_insert_table := 'LOG_DATA_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY') || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM');
v_insert := 'INSERT INTO ' || v_insert_table || ' ('
|| 'LOG_ID,'
|| 'LOG_TIME,'
|| 'APP_ID,'
|| 'MODULE_ID,'
|| 'LOG_TYPE,'
|| 'LOG_CLASS,'
|| 'LOG_LEVEL,'
|| 'USER_ID,'
|| 'USER_IP,'
|| 'URL,'
|| 'MESSAGE'
|| ')'
|| 'SELECT'
|| 'LOG_SEQ.nextval,'
|| 'a.LOG_TIME,'
|| 'a.APP_ID,'
|| 'a.MODULE_ID,'
|| 'a.LOG_TYPE,'
|| 'a.LOG_CLASS,'
|| 'a.LOG_LEVEL,'
|| 'a.USER_ID,'
|| 'a.USER_IP,'
|| 'a.URL,'
|| 'a.MESSAGE'
|| ' FROM LOG_DATA a, ' || v_insert_table || ' b '
|| ' where a.LOG_ID <> b.LOG_ID'
|| ' AND '
|| ' LOG_TIME < TO_DATE(' || v_year || '-' || v_month '-01' || ', 'YYYY-MM-DD') ';
EXECUTE IMMEDIATE (v_insert);
v_delete := 'DELETE FROM LOG_DATA WHERE LOG_TIME < TO_DATE(' || v_year || '-' || v_month '-01' || ', 'YYYY-MM-DD') ';
EXECUTE IMMEDIATE (v_delete);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Procedure ending ...');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Encount database exception ... , so rollback ...');
ROLLBACK;
END transfer_log;
/
需求如下:日志系统,每个月都有一张历史表来存储该月的日志信息,而当月信息存储在当前表中。比如,现在是11月,11月的信息存储在当前表log_data中,10月的信息存储在历史表log_data_200610中,9月的信息存储在历史表log_data_200609中,……,以此类推。这里的当前表和历史表的结构都是相同的,如下:
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_200612)是否存在,如果存在则DBMS_OUTPUT.PUT_LINE('log_data_200612已经存在!')否则创建下个月的历史表(如log_data_200612)。
2、然后从当前表中select上个月的信息(必须要判断select的数据在要插入的历史表中没有重复,否则插不进去),将其插入到上个月的历史表中。(即如果到12月,要把当前表log_data的11月份日志数据insert到历史表log_data_200611中,如果log_id在历史表log_data_200611中存在,则不insert。)然后在当前表log_data中删除已经存在于历史表(如log_data_200611)中的数据,其中包括已经insert成功的和历史表中已经存在的。
3、这个存储过程需要每天晚上00:00:00执行。
==========================================
以下是我现在的进展,估计很可笑,但我还是把它帖出来,希望大家指点,如果真的是朽木不可雕了,那就完全推倒吧!
CREATE OR REPLACE PROCEDURE transfer_log
DECLARE
v_sysdate DATE := SYSDATE;
v_year VARCHAR2(4) := TO_CHAR(v_sysdate, 'YYYY');
v_month VARCHAR2(2) := TO_CHAR(v_sysdate, 'MM');
BEGIN
v_table VARCHAR2(15):= 'LOG_DATA_' || v_year || v_month
IF EXISTS (SELECT * FROM TAB WHERE TNAME = || v_table)
THEN
DBMS_OUTPUT.PUT_LINE(v_table || ' is exists ...');
ELSE
DBMS_OUTPUT.PUT_LINE('create table ' || v_table || '...');
v_string := 'CREATE TABLE ' || v_table || ' ('
|| '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_' || v_table || ' primary key (LOG_ID)'
|| ')';
EXECUTE IMMEDIATE (v_string);
END IF
v_insert_table := 'LOG_DATA_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY') || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM');
v_insert := 'INSERT INTO ' || v_insert_table || ' ('
|| 'LOG_ID,'
|| 'LOG_TIME,'
|| 'APP_ID,'
|| 'MODULE_ID,'
|| 'LOG_TYPE,'
|| 'LOG_CLASS,'
|| 'LOG_LEVEL,'
|| 'USER_ID,'
|| 'USER_IP,'
|| 'URL,'
|| 'MESSAGE'
|| ')'
|| 'SELECT'
|| 'LOG_SEQ.nextval,'
|| 'a.LOG_TIME,'
|| 'a.APP_ID,'
|| 'a.MODULE_ID,'
|| 'a.LOG_TYPE,'
|| 'a.LOG_CLASS,'
|| 'a.LOG_LEVEL,'
|| 'a.USER_ID,'
|| 'a.USER_IP,'
|| 'a.URL,'
|| 'a.MESSAGE'
|| ' FROM LOG_DATA a, ' || v_insert_table || ' b '
|| ' where a.LOG_ID <> b.LOG_ID'
|| ' AND '
|| ' LOG_TIME < TO_DATE(' || v_year || '-' || v_month '-01' || ', 'YYYY-MM-DD') ';
EXECUTE IMMEDIATE (v_insert);
v_delete := 'DELETE FROM LOG_DATA WHERE LOG_TIME < TO_DATE(' || v_year || '-' || v_month '-01' || ', 'YYYY-MM-DD') ';
EXECUTE IMMEDIATE (v_delete);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Procedure ending ...');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Encount database exception ... , so rollback ...');
ROLLBACK;
END transfer_log;
/
3、以用dbms_job的包实现,具体的看看资料吧。就是几个参数的设定。