--存储过程 分钟表 一小时建立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;
/
================================================================================================
明细表
--存储过程 分钟表 一小时建立60张下一小时的表 create or replace procedure p_mi as table_name varch
版权声明
本文来自互联网用户投稿,文章观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处。如若内容有涉嫌抄袭侵权/违法违规/事实不符,请点击 举报 进行投诉反馈!