求助 有点难度 不会写sql语句 能写一个具体点的么
明细表中数据导入天表中去
如果要处理1点表 需要2点之后处理
sysdate 处理前一个小时的60张表
1detail
user1 20
user1 10
user2 30
result
user1 30
user2 30
2detail
user1 10
user1 20
user2 20
result
user1 30
user230
bak
user1 30
user2 30
user1 30
user230
中间表 临时表
day_bak(id,name,times)
loop
insert into day_bak(id,name,times)
select loginname,sum(times) from tableName00-59
group by loginname;
end loop;
insert into day_ select name,sum(times) from bak;
delete bak
1day id name times 一个用户只要唯一一条数据
临时表
事务级 commit 之后数据自动清空
create global temporary table testcom(
id number
) on commit delete rows;
明细表中数据导入天表中去
如果要处理1点表 需要2点之后处理
sysdate 处理前一个小时的60张表
1detail
user1 20
user1 10
user2 30
result
user1 30
user2 30
2detail
user1 10
user1 20
user2 20
result
user1 30
user230
bak
user1 30
user2 30
user1 30
user230
中间表 临时表
day_bak(id,name,times)
loop
insert into day_bak(id,name,times)
select loginname,sum(times) from tableName00-59
group by loginname;
end loop;
insert into day_ select name,sum(times) from bak;
delete bak
1day id name times 一个用户只要唯一一条数据
临时表
事务级 commit 之后数据自动清空
create global temporary table testcom(
id number
) on commit delete rows;
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;
/
================================================================================================