关于Oracle9i定时作业的一个问题

Question

--假设有一张主表,结构如下:
create table VisitLog_20100502
(
  VISITLOGID    NUMBER(19) not null,
  USERNAME      VARCHAR2(16 CHAR) not null,
  IP            VARCHAR2(32 CHAR) not null,
  SERVICENAME   VARCHAR2(32 CHAR) not null,
  REQUESTURL    VARCHAR2(200 CHAR),
  PROCESSFLUX   NUMBER(19), --字节数
  DURATIONTIME  FLOAT,  --一次请求的持续处理时间
  PROCESSRESULT VARCHAR2(16 CHAR),
  MANAGETIME    VARCHAR2(19 CHAR) not null
)示例插入语句：insert into VisitLog(VISITLOGID, USERNAME, IP, SERVICENAME,REQUESTURL, PROCESSFLUX, DURATIONTIME,PROCESSRESULT, MANAGETIME) 
    values(1,'paul','192.168.10.184','Service1', 'http://192.168.10.184/Service1/index.jsp', 3500, 0.3 , '成功', '2010-05-02 08:24:33');
请参照示例插入语句，多插入些数据(如几百条,最好USERNAME，SERVICENAME多列举几个以供统计之用，其中IP和paul是绑定的)--和一张附加表,结构如下:
create table Stat
(
  INFOSTATID  NUMBER(19) not null,
  USERNAME    VARCHAR2(16 CHAR) not null,
  IP          VARCHAR2(32 CHAR) not null,
  SERVICENAME VARCHAR2(32 CHAR) not null,
  PROCESSFLUX NUMBER(19) not null,
  VISITTOTAL  NUMBER(19) not null,
  DATE        DATE not null
)示例插入语句：insert into Stat(InfoStatId, UserName, Ip, ServiceName, ProcessFlux, VisitTotal, Date) values(1,'paul','192.168.10.184','Service1', 3905430, 543, to_date('2010-05-03','yyyy-MM-dd'));
引出问题如下：
建立一个Job,在每天凌晨2点执行.其中Job的功能要求如下：
1).将前一天的访问日志进行相关统计或查询，得到的信息存入到Stat表中。
2).Stat的Date字段取主表的名称中的日期部分进行插入
说明：当Stat中的记录插入正常，隐含了UserName_Ip_ServiceName三个字段不会同时重复的效果！
如：
UserName, Ip, ServiceName,
  u1   .18    s1
  u2 .19  s1
  u1 .18  s2
  u2 .19  s2
此时不能再有
u1  .18   s1
或
u2 .19  s1




希望高手给予指点！！谢谢！

Answer 1

1,如何统计?按UserName, Ip, ServiceName????

Answer 2

insert into Stat
select ... ,to_date(MANAGETIME ,'yyyy-mm-dd hh24:mi:ss') from VisitLog_20100502 
where MANAGETIME >=to_char(trunc(sysdate-1),'yyyy-mm-dd hh24:mi:ss')
and not exists
(select 1 from Stat b where b.UserName=VisitLog_20100502.UserName
and  b.Ip=VisitLog_20100502.ip and  b.ServiceName=VisitLog_20100502.ServiceName
)
group by ...-----统计字段

Answer 3

改下
insert into Stat
select .../*统计字段*/ ,trunc(to_date(MANAGETIME ,'yyyy-mm-dd hh24:mi:ss')) from VisitLog_20100502  
where MANAGETIME >=to_char(trunc(sysdate-1),'yyyy-mm-dd hh24:mi:ss')
and not exists
(select 1 from Stat b where b.UserName=VisitLog_20100502.UserName
and b.Ip=VisitLog_20100502.ip and b.ServiceName=VisitLog_20100502.ServiceName
)
group by ...-----统计字段

Answer 4

总之，STAT表中的记录就是每一天中每个用户(关联IP)和每个服务的相关信息。

Answer 5

那刚才我写了那么多，有些错了。那个not exists 后的可以去掉了，
就是个按多字段的group by ,
然后该sum该count,楼主自己写吧 

Answer 6

顺便再问一个问题，
假设我定义了一个自定义的record数组declare 
     type myrecord is record(
       is_userName VARCHAR2(16 CHAR),
       is_ip VARCHAR2(32 CHAR),
       is_visitTotal NUMBER(19),
       is_serviceName VARCHAR2(32 CHAR),
       is_processFlux NUMBER(19),
       is_date DATE
     );
     
     type myrecords is varying array(2) of myrecord;
         
begin

--select 表名 返回多条记录(不一定是2条时)
--应该如何存入这个数组之中呢？

end;

Answer 7

--填充数组
Select  is_userName ,       is_ip ,       is_visitTotal,
       is_serviceName ,      is_processFlux ,   is_date 
into myrecords
From tab

Answer 8

我试了，没有用。
myrecords是一个类型，就算用myrecords声明一个变量如myr的话，这样类似操作也还是没用！！
而且我select操作还带有集函数！

Answer 9

添加一个唯一约束alter table Stat
add constraint UN_Stat unique(USERNAME,IP,SERVICENAME)

Answer 10

declare 
     tableName varchar2(30):='EZSM_SERVICEVISITLOG_' || replace(to_char(trunc(sysdate-1),'yyyy-MM-dd'),'-','');
     fromStatement varchar2(50):=' from ' || tableName;
     strSQL varchar2(4000);
     maxId number(19);
begin
    select max(is_infostatId) into maxId from Ezsm_Infostat;
    for r_temp in (select distinct(svl_userName) f1
                          ,svl_ip f2
                          ,count(svl_userName) f3 
                          ,svl_serviceName f4
                          ,sum(svl_processFlux) f5 
                          ,to_date(trunc(sysdate)) f6 
                   from EZSM_SERVICEVISITLOG_20100430 
                   group by svl_userName,svl_ip,svl_serviceName) loop
        maxId:=maxId+1; --插入前先加一，以免主键重复         
        insert into EzSM_InfoStat(is_infostatId,is_userName,is_ip,is_visitTotal,is_serviceName,is_processFlux,Is_Date)
            values(maxId, r_temp.f1, r_temp.f2, r_temp.f3, r_temp.f4, r_temp.f5, r_temp.f6); 
    end loop; 
    commit; --提交
     dbms_output.put_line('tableName=' || tableName || ', maxId=' || maxId);
     --execute immediate strSQL;
 exception
    when others then
    dbms_output.put_line('error!');
 end;
请问我如何把
EZSM_SERVICEVISITLOG_20100430
替换成变量tableName并有效执行呢？

Answer 11

使用动态游标sys_refcursor
例如
declare
cur sys_refcursor;
v1 varchar2(30);
begin
open cur for 'select table_name from'||' user_tables';
loop
fetch cur into v1;
exit when cur%notfound;
....
end loop;
close cur;
end;

Answer 12

to: wildwave所有相关问题均已解决！！谢谢！！