明细表

--存储过程 分钟表 一小时建立60张下一小时的表 create or replace procedure p_mi as   table_name varch
--存储过程 分钟表 一小时建立60张下一小时的表
create or replace procedure p_mi
as
  table_name varchar2(20);
  start_date date;
  end_date date;
  v_sql varchar2(500);
begin
  start_date:=trunc(sysdate,'hh24')+1/24;           -- 分钟 :当前时间的下一个小时后 开始
  end_date:=trunc(sysdate,'hh24')+1/12;             -- 分钟 :当前时间的两个小时后 结束
  while start_date<>end_date 
  loop
    table_name:='detail_'||to_char(start_date,'yyyymmddhh24mi');  
        
    v_sql:='create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
    dbms_output.put_line(v_sql||'==');
    
    execute immediate 'create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
  start_date:=start_date+1/(24*60);  
end loop;
end;
/




declare
  v_jobmi number;  
begin
  dbms_job.submit(v_jobmi,'p_mi;',trunc(sysdate,'hh24')+1*10/(24*60),'sysdate+1/24');
end;
/
--查询v_jobmi号    '1'
select job,what from user_jobs;
--手动job
execute dbms_job.run(1);
查询表
select TABLE_NAME from user_tables where table_name like '%DETA%';


====================================================================================================
--存储过程 小时表 一当天建立下一天的24个表 小时
create or replace procedure p_hh
as
  table_name varchar2(20);
  start_date date;
  end_date date;
  v_sql varchar2(500);
begin
  start_date:=trunc(sysdate,'dd')+1;           -- 分钟 :当前时间的下一天小时后 开始
  end_date:=trunc(sysdate,'dd')+2;             -- 分钟 :当前时间的两天后 结束
  while start_date<>end_date 
  loop
    table_name:='detail_'||to_char(start_date,'yyyymmddhh24');  
        
    v_sql:='create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
    dbms_output.put_line(v_sql||'==');
        execute immediate 'create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
  start_date:=start_date+1/24;  
  end loop;
end;
/




declare
  v_jobhh number;  
begin
  dbms_job.submit(v_jobhh,'p_hh;',trunc(sysdate,'dd')+1*11/(24),'sysdate+1');
end;
/
===========================================================================================================


--创建天表  这个月的某天某时创建下个月的30张表


create or replace procedure p_dd
as
  table_name varchar2(20);
  start_date date;
  end_date date;
  v_sql varchar2(500);
begin
  start_date:=add_months(trunc(sysdate,'mm'),1);           -- 分钟 :当前时间的下一个月 开始
  end_date:=add_months(trunc(sysdate,'mm'),2);             -- 分钟 :当前时间的两个月后 结束
  while start_date<>end_date 
  loop
    table_name:='detail_'||to_char(start_date,'yyyymmdd');  
        
    v_sql:='create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
    dbms_output.put_line(v_sql||'==');
        execute immediate 'create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
  start_date:=start_date+1;  
  end loop;
end;
/




declare
  v_jobdd number;  
begin
  dbms_job.submit(v_jobdd,'p_dd;',trunc(sysdate,'mm')+27+1*10/24,'add_months(sysdate,1)');
end;
/


===========================================================================================================
--创建月表 当年某月某天某日某时创建 下一年的12各表
create or replace procedure p_mm
as
  table_name varchar2(20);
  start_date date;
  end_date date;
  v_sql varchar2(500);
begin
  start_date:=add_months(trunc(sysdate,'yyyy'),12);           -- 分钟 :当前时间的下一年 开始
  end_date:=add_months(trunc(sysdate,'yyyy'),24);             -- 分钟 :当前时间的两年后 结束
  while start_date<>end_date 
  loop
    table_name:='detail_'||to_char(start_date,'yyyymm');  
        
    v_sql:='create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
    dbms_output.put_line(v_sql||'==');
        execute immediate 'create table '||table_name||'(id number,loginName varchar2(20),times number,loginpassword varchar2(20),beforedate timestamp,afterdate timestamp)';
  start_date:=add_months(start_date,1);
  end loop;
end;
/




declare
  v_jobmm number;  
begin
  dbms_job.submit(v_jobmm,'p_mm;',add_months(trunc(sysdate,'yyyy'),12)-5,'add_months(sysdate,12)');
end;
/
========================================================================================
--创建临时表linshi
create global temporary table linzhi(
       id number,
       loginName varchar2(20),
       times number,
       loginpassword varchar2(20),
       beforedate timestamp,
       afterdate timestamp
)on commit delete rows;


========================================================================================
--整合分钟表存储过程   一小时后整合前一小时的数据    用户名一样的 时间段 和


create or replace procedure pzh_mm
as
       start_date date:=trunc(sysdate,'hh24')-1/24;
       end_date date:=trunc(sysdate,'hh24');
       table_namehh varchar(50);
       table_name varchar2(20);


begin
       table_namehh:='detail_'||to_char(start_date,'yyyymmddhh24');
       while start_date<>end_date loop
       table_name:='detail_'||to_char(start_date,'yyyymmddhh24mi');
       start_date:=start_date+1/(24*60);
       execute immediate 'insert into linshi(loginName,times) select loginName,sum(times) from '||table_name||'ground by loginName';       
       end loop;
       execute immediate 'insert into '||table_namehh||' (loginName,times) select loginName,sum(times) from linshi ground by loginName';
end;
/


declare 
    v_job_pmm number;
begin
    dbms_job.submit(v_job_pmm,'pzh_mm;',trunc(sysdate,'hh24')+1*20/(24*60),'sysdate+1/24');
end;
/   


=====================================================================================================================
--整合小时表 每天的某时某刻 整合前一天的24小时的表数据
create or replace procedure pzh_hh
as
       start_date date:=trunc(sysdate,'dd')-1;
       end_date date:=trunc(sysdate,'dd');
       table_namedd varchar(50);
       table_name varchar2(20);


begin
       table_namedd:='detail_'||to_char(start_date,'yyyymmdd');
       while start_date<>end_date loop
       table_name:='detail_'||to_char(start_date,'yyyymmddhh24');
       start_date:=start_date+1/24;
       execute immediate 'insert into linshi(loginName,times) select loginName,sum(times) from '||table_name||'ground by loginName';       
       end loop;
       execute immediate 'insert into '||table_namedd||' (loginName,times) select loginName,sum(times) from linshi ground by loginName';
end;
/


declare 
    v_job_phh number;
begin
    dbms_job.submit(v_job_phh,'pzh_hh;',trunc(sysdate,'dd')+1*8/24,'sysdate+1');
end;
/    
 ============================================================================================================      
--整合天表  每个月的某天某时某刻 整合上个月的表
create or replace procedure pzh_dd
as
       start_date date:=add_months(trunc(sysdate,'mm'),-1);
       end_date date:=trunc(sysdate,'mm');
       table_namedd varchar(50);
       table_name varchar2(20);
       table_namemm varchar(50);


begin
       table_namemm:='detail_'||to_char(start_date,'yyyymm');
       while start_date<>end_date loop
       table_name:='detail_'||to_char(start_date,'yyyymmdd');
       start_date:=start_date+1/24;
       execute immediate 'insert into linshi(loginName,times) select loginName,sum(times) from '||table_namedd||'ground by loginName';       
       end loop;
       execute immediate 'insert into '||table_namemm||' (loginName,times) select loginName,sum(times) from linshi ground by loginName';
end;
/


declare 
    v_job_pdd number;
begin
    dbms_job.submit(v_job_pdd,'pzh_dd;',trunc(sysdate,'mm')+1*8/24,'add_months(sysdate,1)');
end;
/            
       
 ================================================================================================