史上最簡單的數據抽取

史上最簡單的數據抽取

    做為一名全職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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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開發方向的朋友們、

        送給想和做不同步的朋友們、

        送給想學習的朋友們

    附：本篇文章中的代碼，全部手工測試過沒有問題。如果朋友們在操作過程中發現報錯，請好好檢查一下代碼。