建表
CREATE TABLE LOG.LOG_ACTIVE_MOUTH
( "CLIENTID" VARCHAR2(38) DEFAULT sys_guid(),
"CLIENTIP" VARCHAR2(36),
"ENTERTIME" DATE,
"USERID" NVARCHAR2(100),
"SSDWDM" NUMBER(20,0)
)
创建存储过程
create or replace procedure LOG.PRO_LOG_ACTIVE_MOUTH is
Type depCur is ref cursor;
cur depCur;
V_SQL varchar2(2000);
V_DATE DATE;
Part varchar2(10);
V_USER_SOURCE varchar2(100);
V_USERID varchar2(50);
V_SSDWDM varchar2(20);
begin
V_DATE := ADD_MONTHS(sysdate, -1);
Part := 'p' || to_char(V_DATE, 'yyyymm');
V_SQL := 'select TCIP.userip,TCIP.userid,TCIP.ssdwdm from (select userip,max(userid) as userid,max(ssdwdm) as ssdwdm from LOG.SYS_LOG partition(' || Part || ') group by userip) TCIP ';
open cur for V_SQL ;
loop
FETCH cur INTO V_USER_SOURCE, V_USERID, V_SSDWDM; --取值
EXIT WHEN cur%NOTFOUND; --当没有记录时退出循环
insert into log.LOG_ACTIVE_MOUTH (clientip, entertime, userid, ssdwdm)
values (V_USER_SOURCE, V_DATE, V_USERID, V_SSDWDM);
END LOOP;
CLOSE cur;
commit;
end PRO_LOG_ACTIVE_MOUTH;
/
设置自动执行
grant create job to a4log;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'LOG_ACTIVE_MOUTH',
job_type => 'STORED_PROCEDURE',
job_action => 'PRO_LOG_ACTIVE_MOUTH',
start_date => sysdate,
repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1;ByHour=01;ByMinute=00',
auto_drop => false,
enabled => true,
comments =>'采集上个月活跃数据');
END;
/
exec dbms_scheduler.drop_job('LOG_ACTIVE_MOUTH');
在这里用到了
1,动态游标
2,动态sql
3,存储过程
4,scheduler job
记录供以后参考