当前位置: 开发笔记 > 数据库 > 正文

史上最简单的数据抽取

作者：晓辉19890424俱乐部 | 来源：互联网 | 2018-05-27 21:57

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

史上最简单的数据抽取

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

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

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

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

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

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

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

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

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

那么什么是数据抽取？

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

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

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

(二)、模拟业务需求

(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、创建日志表(存储抽取进度)

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

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调用存储过程时报没有权限

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

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

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

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

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

常用的定时任务有2种：

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

b.dbms_job (oracle自带的)

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

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

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

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

1、SQL多表联合查询

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

3、数据的自动抽取

4、数据抽取的进度监控

5、定时任务

6、表的管理

结束语：

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

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

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

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

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

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

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

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

送给想学习的朋友们

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

推荐阅读

数据库
优化DB2数据库性能的关键策略

本文详细介绍了优化DB2数据库性能的多种方法，涵盖统计信息更新、缓冲池调整、日志缓冲区配置、应用程序堆大小设置、排序堆参数调整、代理程序管理、锁机制优化、活动应用程序限制、页清除程序配置、I/O服务器数量设定以及编入组提交数调整等方面。通过这些技术手段，可以显著提升数据库的运行效率和响应速度。 ... [详细]

蜡笔小新 2024-12-22 16:20:33
sql
利用SAS生成动态SQL查询

本文介绍如何使用SAS根据输入数据集自动生成并执行SQL查询，其中CASE语句依据另一个数据集中的观测值动态调整。 ... [详细]

蜡笔小新 2024-12-22 15:47:35
database
解决Windows和SSMS中未注册'microsoft.ACE.oledb.12.0'提供程序的问题

本文介绍了解决在Windows操作系统或SQL Server Management Studio (SSMS) 中遇到的“microsoft.ACE.oledb.12.0”提供程序未注册问题的方法，特别针对Access Database Engine组件的安装。 ... [详细]

蜡笔小新 2024-12-21 15:21:22
数据库
PostgreSQL 最新动态 —— 2022年4月6日

了解 PostgreSQL 社区的最新进展和技术分享 ... [详细]

蜡笔小新 2024-12-21 14:54:10
数据库
MySQL中Bin Log与Redo Log的区别及应用场景

本文详细介绍了MySQL数据库中的Bin Log和Redo Log，阐述了它们在日志记录机制、应用场景以及数据恢复方面的区别。通过对比分析，帮助读者更好地理解这两种日志文件的作用和特性。 ... [详细]

蜡笔小新 2024-12-21 13:50:51
数据库
SP框架数据操作指南

本章详细介绍SP框架中的数据操作方法，包括数据查找、记录查询、新增、删除、更新、计数及字段增减等核心功能。通过具体示例和详细解析，帮助开发者更好地理解和使用这些方法。 ... [详细]

蜡笔小新 2024-12-21 13:27:32
sqlserver
SQL Server 登录验证模式的修改方法：混合验证模式详解

在安装 SQL Server 时，选择混合验证模式可以提供更高的灵活性和管理便利性。如果您已经安装了 SQL Server 并使用单一的 Windows 身份验证模式，可以通过以下步骤将其更改为混合验证模式。 ... [详细]

蜡笔小新 2024-12-21 11:32:52
sql
SQL Server 2012 客户端 SQL 格式化工具与方法

本文介绍了在 SQL Server 2012 客户端中格式化 SQL 查询语句的多种方法，包括内置功能和第三方工具，帮助用户提高代码可读性和维护性。 ... [详细]

蜡笔小新 2024-12-21 10:59:16
oracle
SQL Server 中 INSERT 语句无法插入数据的问题分析与解决

本文探讨了在 SQL Server 中使用 JDBC 插入数据时遇到的问题。通过详细分析代码和数据库配置，提供了解决方案并解释了潜在的原因。 ... [详细]

蜡笔小新 2024-12-21 09:52:27
redis
优化Flask应用的并发处理：解决Mysql连接过多问题

本文探讨了在Flask应用中通过优化后端架构来应对高并发请求，特别是针对Mysql 'too many connections' 错误的解决方案。我们将介绍如何利用Redis缓存、Gunicorn多进程和Celery异步任务队列来提升系统的性能和稳定性。 ... [详细]

蜡笔小新 2024-12-21 09:21:49
数据库
优化SQL Server批量数据插入存储过程的实现

本文介绍了一种改进的SQL Server存储过程，用于生成批量插入语句。该方法不仅提高了性能，还支持单行和多行模式，适用于SQL Server 2005及以上版本。 ... [详细]

蜡笔小新 2024-12-21 06:43:52
数据库
使用JS、HTML5和C3创建自定义弹出窗口

本文介绍如何结合JavaScript、HTML5和C3.js来实现一个功能丰富的自定义弹出窗口。通过具体的代码示例，详细讲解了实现过程中的关键步骤和技术要点。 ... [详细]

蜡笔小新 2024-12-20 21:22:27
oracle
主调|大侠_重温C++

主调|大侠_重温C++ ... [详细]

蜡笔小新 2024-12-20 20:43:56
redis
Java SpringMVC SSM 实现多模块集成：操作日志、文件管理、头像编辑、权限控制及缓存优化

本文介绍了一个基于 Java SpringMVC 和 SSM 框架的综合系统，涵盖了操作日志记录、文件管理、头像编辑、权限控制、以及多种技术集成如 Shiro、Redis 等，旨在提供一个高效且功能丰富的开发平台。 ... [详细]

蜡笔小新 2024-12-20 19:17:47
oracle
使用正则表达式去除字符串中单词间的空格

本文探讨了如何在Hive（基于Hadoop）环境中编写类似SQL的语句，以去除字段中的空格。特别是在处理邮政编码等数据时，去除特定位置的空格是常见的需求。 ... [详细]

蜡笔小新 2024-12-20 19:08:43

晓辉19890424俱乐部

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章