登录  /  注册
首页 > 数据库 > mysql教程 > 正文
史上最简单的数据抽取
php中文网
发布： 2016-06-07 14:56:10
原创
1468人浏览过

史上最简单的数据抽取 做为一名全职DBA，在之前的工作中，常会收到这样的需求，需要我配合开发定时的从几张目标表取出他们需要的数据，并存放到临时表中，开发们再从临时表中取出数据展现给前端页面。 收到这样的需求，我觉得应该考虑以下几点： (1)目标表有

史上最简单的数据抽取

做为一名全职DBA，在之前的工作中，常会收到这样的需求，需要我配合开发定时的从几张目标表取出他们需要的数据，并存放到临时表中，开发们再从临时表中取出数据展现给前端页面。

收到这样的需求，我觉得应该考虑以下几点：

(1)目标表有哪几张，它们的关联关系如何，这决定了我如何取数据

(2)定时抽取，意味着要周期性的提取数据，此抽取周期是多少？每次抽取数据的频率是多久？

(3)周期性的抽取数据，那么临时表的命名要有规则，通过就是"临时表名_日期时间"，这样命名方便开发前端取数

(4)周期性的抽取数据，那么临时表的保留及清理也要考虑一下，防止表空间使用率过高

(5)如果每一次抽取数据消耗的时间比较长，那么要有1个监控的手段，方便查看本次抽取数据的进度

(6)数据抽取的过程中，如果本次抽取数据时发现有问题，那么你抽数的存储过程要可以复用。

也就是说，当前你第2次执行抽数的存储过程时，本次操作之前抽取出的脏数据要清空掉。

那么什么是数据抽取？

最简单的解释就是，从原始数据中抽取出感兴趣数据的过程。

针对上面我总结出的6点，咱们开始模拟一套最简单的数据抽取案例。

(一)、表结构及字段说明

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210132050-0.jpg" style="max-width:90%" title="部门表.png" alt="wKiom1WOSSaSw7E1AAKe9uJmSkA271.jpg" />

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210131Y7-1.jpg" style="max-width:90%" title="员工表.png" alt="wKioL1WOSubDwyk4AAN2ODh7XpU924.jpg" />

(二)、模拟业务需求

(1)、从emp和dept表中抽取出：emp.empno、emp.ename、emp.job、emp.deptno、dept.dname、dept.loc、sysdate字段，构造产生临时表：T_EMP_DEPT
(2)、emp和dept表的关联关系：emp表的deptno字段  参照引用  dept表的deptno字段
(3)、开发人员每天13点会查询使用当天产生的临时表
(4)、每次产生的临时表，保留周期是30天，超过30天的临时表可以被清理掉
登录后复制

(三)、给出解决方案

(1)、从emp和dept表关联查询出需要的字段，关联字段是deptno，并创建临时表：T_EMP_DEPT
(2)、每天抽取一次，我们在每天13点之前把临时表创建好就可以了
(3)、临时表的命名规则：T_EMP_DEPT_yyyymmdd(取当前系统的年月日)
(4)、抽取数据的存储过程中，加入逻辑判断，取当前系统时间，并将30天前的临时表清除(先truncate，再drop)
(5)、单独写1个存储过程及表，用来保存每次抽取数据的进度情况，方便我们监控抽取数据的进度
(6)、在抽取数据的存储过程中，每次都要先truncate临时表、drop临时表，然后再进行本次的数据抽取。实现数据抽取的复用
登录后复制

既然全想清楚了，那么我们就开始操练起来吧

1、创建日志表(存储抽取进度)

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210133505-2.jpg" title="日志表.png" alt="wKioL1WOS2GjvszPAAUrB8R9cvE643.jpg" />

2、创建监控进度的存储过程

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210134045-3.jpg" title="监控抽取进度存储过程.png" alt="wKiom1WOSdfCkIcaAASKc1-toA4015.jpg" />

3、创建抽取数据的存储过程

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语句 
  insert_sql   varchar2(5000);--Insert操作SQL语句 
  date_30  varchar2(20); --数据过期的时间 30天以前
  date_cur varchar2(20); --当前日期
  log_detail varchar2(4000); --日志明细参数
  
begin
  date_cur   := to_char(sysdate, 'yyyymmdd'); --当前日期
  date_30    := to_char(sysdate - 30, 'yyyymmdd'); --30天以前的日期
  table_flag   := 0; --初始状态0，目标不存在
  table_name_1 := 'T_EMP_DEPT'||date_cur;
  ---如果临时表存在先清空、再干掉(实现功能复用)
  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_DEPTyyyymmdd
 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','抽取数据部分结束了!');
    
  ---删除30天以前的临时表
  execute immediate 'select count(*) from user_TABLES 
  where table_name=''T_EMP_DEPT'||date_30 || '''' into table_flag;
  
  if table_flag = 1 then     --找到了30天前的表
    execute immediate ' truncate table T_EMP_DEPT' ||date_30;
    execute immediate ' drop table T_EMP_DEPT' ||date_30;
    
    log_detail := '删除30天前临时表完成时间:' ||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    P_INSERT_LOG(sysdate,'prc_emp_dept','23',log_detail);  
  end if;   
    
end prc_emp_dept;
/
登录后复制

4、使用sys用户显示授权给scott,防止dbms_job调用存储过程时报没有权限

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210134338-4.jpg" title="授权scott.jpg" alt="wKioL1WOTAKRpoGZAAHXpVeEOVM888.jpg" />

5、查看监控日志表(新表什么也没有)

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210132P5-5.jpg" title="日志表没记录.png" alt="wKiom1WOSmbw-b2JAAJxMjK9lKg042.jpg" />

6、手工执行抽取数据的存储过程

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210133927-6.jpg" title="手工执行存储过程.png" alt="wKiom1WOSpHg2MX3AAJZtn0JhQs370.jpg" />

7、查看监控日志表(记录得比较详细)

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/121013K41-7.jpg" title="日志表有记录.png" alt="wKioL1WOTGviF8ReABDY6ps0lqU349.jpg" />

朋友们，咱们的数据抽取功能、监控抽取进度、业务的需求咱们基本就全完成了。

现在还差1项就是把抽取数据的存储过程设置成定时任务，然后周期性的执行。

常用的定时任务有2种：

a.crontab (操作系统层面的)

b.dbms_job (oracle自带的)

将咱们的数据抽取存储过程加入到定时任务，让它自己周期性的执行就可以了。

8、我使用的是dbms_job，详细如下图

650) this.width=650;" src="http://www.68idc.cn/help/uploads/allimg/151111/1210131932-8.jpg" title="定时任务.png" alt="wKiom1WOSySDpeRVAAVlYlJyX0c416.jpg" />

好了，朋友们至此，本套史上最简单的数据自动抽取文章就结束了！

此篇文章中涵盖了以下知识点：

1、SQL多表联合查询

2、关于业务的分析及思考

3、数据的自动抽取

4、数据抽取的进度监控

5、定时任务

6、表的管理

结束语：

相信大家已经发现了，本次文章中使用到的操作用户是scott，咱们oracle中基础练习的用户。

本篇文章是我上课的一个小案例，我的学生们反应不错，他们说接受起来比较容易。

所以我就把这个小案例移植到了51的博客上，分享给更多需要的朋友们！

其实每个人都有拖延症，都会害怕去尝试新鲜事物，所以我想说本篇文章：

送给想做数据抽取的朋友们、

送给对数据处理感兴趣的朋友们、

送给想学习oracle开发方向的朋友们、

送给想和做不同步的朋友们、

送给想学习的朋友们

附：本篇文章中的代码，全部手工测试过没有问题。如果朋友们在操作过程中发现报错，请好好检查一下代码。


相关标签：
史上 最简单 数据 抽取 史上 最简单 数据 抽取 做为 一
来源：php中文网
上一篇：/dev/sr0光驱挂载引起的CPUio_wait升高一“血案” 下一篇：MySQL中AES_ENCRYPT('密码','钥匙')
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
在基于类的组件中更新React页面而不使用useState。 我正在使用React的基于类的组件来首先显示所有的令牌。然后，我想要同时显示两个具有不考虑顺序的带有span id的文本，使用上一个和下一个按钮来重新渲染页面。重新渲染页面将有 *...
P粉739886290来自于2023-08-03 21:16:09
0 1 302
故障排除：初始登录和网站加载问题 我在我的Vue应用程序中使用VueApollo和Vuex时遇到了问题，所以当我首次登录网站时，网页是空的，即不包含事件和身份验证，但是当我使用浏览器中的刷新按钮刷新网站时，所有事件...
P粉891237912来自于2023-08-03 21:02:37
0 1 197
在VueJS中使用JS定义的名称作为CSS变量的解决方案。 我正在使用Vuetify，它为每个主题颜色（如--v-theme-primary）创建CSS变量。我希望能够在CSS中设置颜色为--v-theme-{something}，并且{s...
P粉333395496来自于2023-08-03 20:20:26
0 1 245
在Vue.js 3中通过排序、过滤和分页组件增强表格功能。 我已经进行了一段时间的研究，但似乎找不到一个适用于Vue.js 3、可以为表格添加排序、过滤和分页功能的最新组件。前端已经拥有了所有数据，不需要向后端API发送额外的请求。到目前为...
P粉419164700来自于2023-08-03 20:02:22
0 1 203
在Vue Router中为可选URL参数设置默认值，同时具有独立的可选URL参数。 我有以下路由： export const personRoutes: RouteRecordRaw[] = [ { path: '/person/:id?/:...
P粉377412096来自于2023-08-03 19:31:32
0 1 169
在Vue中使用localStorage。在哪里需要使用它？ 我想使用本地存储，这样当页面重新加载时，数据能够保存下来。我使用Vuex来判断一个人是否已注册。我希望当页面加载时，这个人已经注册。我的Vuex代码如下：export defaul...
P粉122932466来自于2023-08-03 18:43:43
0 1 141
下载的VScode界面与讲课视频里的不一样，也没有自动显示终端和帮助，还有一张幻灯片就很迷惑
P粉209771605来自于2023-08-04 17:56:21
0 0 44
实现Vue加载动画，同时从URL加载图片 当图片加载时，我想要显示一个加载动画，但是我不知道如何实现。虽然我进行了调试，并在控制台输出了"true"和"false"，但是加载动画仍然...
P粉021854777来自于2023-08-03 18:04:16
0 1 273
在Vue.js中创建一个用于不存在组件的错误。 我想生成一个错误，而不是像在控制台中显示的简单警告那样，当我们尝试使用一个不存在的组件时：[Vue警告]:无法解析组件：nonexisting-component。如果这是一个原生...
P粉765570115来自于2023-08-03 17:43:26
0 1 219
比较 JavaScript 的 Date 对象 在 JavaScript 中比较日期对象时，我发现即使是比较相同的日期也不会返回 true。例如： var startDate1 = new Date("02/10...
P粉724737511来自于2023-08-03 17:28:16
0 2 202
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 技术文章
php中文网：公益在线php培训，帮助PHP学习者快速成长！
关注服务号 技术交流群
app下载
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2023 //m.sbmmt.com/ All Rights Reserved | 苏州跃动光标网络科技有限公司 | 苏ICP备2020058653号-1

 | 本站CDN由 数掘科技 提供

登录PHP中文网，和优秀的人一起学习！
全站2000+教程免费学
微信扫码登录

  • 精品班

  • 立即咨询

  • 微信沟通

  • QQ群

  • 优惠券

  • 返回顶部