day13_自动抽取数据——存储过程编写
emp和dept产生临时数据1
CREATE OR REPLACE PROCEDURE prc_emp_dept authid current_user is
table_name_1 varchar2(100); --表名
table_flag number; --表是否存在 0:不存在 1:存在
create_sql varchar2(5000);--创建中间表的SQL语句
begin
table_flag := 0; --初始状态0，目标表不存在
table_name_1 := 'T_EMP_DEPT';
---如果中间表存在先清空、再干掉
execute immediate 'select count(*) from user_tables where table_name='''||table_name_1|| '''' into table_flag;
if table_flag = 1 then
execute immediate 'truncate table '||table_name_1;
execute immediate 'drop table '||table_name_1;
end if;
---创建中间表T_WLM_SOLR_DATA
create_sql :='create table '|| table_name_1 || ' nologging as
select
e.EMPNO ,
e.ENAME ,
e.JOB ,
e.MGR ,
e.HIREDATE,
e.SAL ,
e.COMM ,
e.DEPTNO ,
d.DNAME ,
d.LOC ,
sysdate as current_time
from emp e,dept d where e.deptno=d.deptno';
execute immediate create_sql;
end prc_emp_dept;
/
emp和dept产生临时数据2
CREATE OR REPLACE PROCEDURE prc_emp_dept authid current_user is
table_name_1 varchar2(100); --表名1
table_flag number; --表是否存在 0:不存在 1:存在
create_sql varchar2(5000);--创建中间表的SQL语句
insert_sql varchar2(5000);--Insert操作SQL语句
begin
table_flag := 0; --初始状态0，目标不存在
table_name_1 := 'T_EMP_DEPT';
---如果中间表存在先清空、再干掉
execute immediate 'select count(*) from user_tables where table_name='''||table_name_1|| '''' into table_flag;
if table_flag = 1 then
execute immediate 'truncate table '||table_name_1;
execute immediate 'drop table '||table_name_1;
end if;
---创建中间表T_EMP_DEPT
create_sql :='create table '|| table_name_1 || ' nologging as
select
e.EMPNO ,
e.ENAME ,
e.JOB ,
e.MGR ,
e.HIREDATE,
e.SAL ,
e.COMM ,
e.DEPTNO ,
d.DNAME ,
d.LOC ,
sysdate as current_time
from emp e,dept d where e.deptno=d.deptno';
execute immediate create_sql;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
end prc_emp_dept;
/
emp和dept产生临时数据3【精简监控】
CREATE OR REPLACE PROCEDURE prc_emp_dept authid current_user is
table_name_1 varchar2(100); --表名1
table_flag number; --表是否存在 0:不存在 1:存在
create_sql varchar2(5000);--创建中间表的SQL语句
insert_sql varchar2(5000);--Insert操作SQL语句
log_detail varchar2(4000); --日志明细参数
begin
table_flag := 0; --初始状态0，目标不存在
table_name_1 := 'T_EMP_DEPT';
---如果中间表存在先清空、再干掉
execute immediate 'select count(*) from user_tables where table_name='''||table_name_1|| '''' into table_flag;
if table_flag = 1 then
execute immediate 'truncate table '||table_name_1;
execute immediate 'drop table '||table_name_1;
--日志明细信息
log_detail := '删除旧中间表时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','00',log_detail);
end if;
---创建中间表T_EMP_DEPT
create_sql :='create table '|| table_name_1 || ' nologging as
select
e.EMPNO ,
e.ENAME ,
e.JOB ,
e.MGR ,
e.HIREDATE,
e.SAL ,
e.COMM ,
e.DEPTNO ,
d.DNAME ,
d.LOC ,
sysdate as current_time
from emp e,dept d where e.deptno=d.deptno';
execute immediate create_sql;
--日志明细信息
log_detail := '中间表创建完毕时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','01',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第1次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','02',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第2次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','03',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第3次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','04',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第4次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','05',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第5次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','06',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第6次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','07',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第7次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','08',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第8次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','09',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第9次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','10',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第10次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','11',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第11次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','12',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第12次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','13',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第13次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','14',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第14次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','15',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第15次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','16',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第16次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','17',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第17次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','18',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第18次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','19',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第19次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','20',log_detail);
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := '第20次往中间表插入数据完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
P_INSERT_LOG(sysdate,'prc_emp_dept','21',log_detail);
P_INSERT_LOG(sysdate,'prc_emp_dept','22','抽取数据结束了!');
end prc_emp_dept;
/
emp和dept产生临时数据3【生产监控】
CREATE OR REPLACE PROCEDURE prc_emp_dept authid current_user is
table_name_1 varchar2(100); --表名1
table_flag number; --表是否存在 0:不存在 1:存在
create_sql varchar2(5000);--创建中间表的SQL语句
insert_sql varchar2(5000);--Insert操作SQL语句
begin_time date; --日志执行时间参数
log_detail varchar2(4000); --日志明细参数
begin
begin_time := sysdate;
table_flag := 0; --初始状态0，目标不存在
table_name_1 := 'T_EMP_DEPT';
---如果中间表存在先清空、再干掉
execute immediate 'select count(*) from user_tables where table_name='''||table_name_1|| '''' into table_flag;
if table_flag = 1 then
execute immediate 'truncate table '||table_name_1;
execute immediate 'drop table '||table_name_1;
--日志明细信息
log_detail := log_detail || '删除旧中间表时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
end if;
---创建中间表T_EMP_DEPT
create_sql :='create table '|| table_name_1 || ' nologging as
select
e.EMPNO ,
e.ENAME ,
e.JOB ,
e.MGR ,
e.HIREDATE,
e.SAL ,
e.COMM ,
e.DEPTNO ,
d.DNAME ,
d.LOC ,
sysdate as current_time
from emp e,dept d where e.deptno=d.deptno';
execute immediate create_sql;
--日志明细信息
log_detail := log_detail || '中间表创建完毕时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第1次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第2次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第3次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第4次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第5次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第6次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第7次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第8次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第9次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第10次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第11次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第12次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第13次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第14次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第15次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第16次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第17次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
insert_sql :='insert into '|| table_name_1 ||' select * from '|| table_name_1;
execute immediate insert_sql;
commit;
--日志明细信息
log_detail := log_detail || '第18次往中间表插入数据完成时间:' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') || '|';
P_INSERT_LOG(begin_time,
'prc_emp_dept',
to_char(sysdate, 'yyyy-mm-dd'),
log_detail);
end prc_emp_dept;
/
