笔记：记录一次搜集月活数据

建表

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

记录供以后参考