ETL通用解決方案oracle+存儲(chǔ)過程實(shí)現(xiàn)

1、ETL通用解決方案一一oracle+存儲(chǔ)過程實(shí)現(xiàn)方案理念 - 四化理念數(shù)據(jù)規(guī)格化處理自動(dòng)化信息集中化操作人性化架構(gòu)通過視圖實(shí)現(xiàn)來至兩個(gè)不同數(shù)據(jù)庫的表的結(jié)構(gòu)完全一致, 在結(jié)構(gòu)完全相同的兩個(gè)表之間進(jìn)行數(shù)據(jù)同步 , 問題變得相當(dāng)簡單. 同步代碼如下.ETL- 初始同步delete from ods_table;insert into v_tableselect * from db_table;commit;- 新增同步insert into v_tableselect * from db_table twhere t.id not in (select id from v_table);commit

2、;-變更同步update ods_table t set t.c_number = (select db.c_number from db_table db where db.id = t.id) where t.c_number != (select db.c_number from db_table db where db.id = t.id);commit;實(shí)現(xiàn)兩個(gè)表結(jié)構(gòu)完全一致的方法如下建表CREATE SEQUENCE SEQ ETL INCREASE IDINCREMENT BY 1START WITH 1NOCACHE;*/* /* Table:ETL_TABLES*/*/*CR

3、EATE TABLE ETL_TABLES(IDNUMBERDEFAULT -1 NOT NULL,TABLE_NAMENOT NULL,TABLE_TYPE NOT NULL,TABLE_ROOT_INVARCHAR2(100)VARCHAR2(30)VARCHAR2(30),TABLE_NEED_CREATE_VIEWNUMBERDEFAULT 1,TABLE_CREATE_VIEW_NAME_PREFIXVARCHAR2(30)DEFAULT v,DB_LINK_NAMECURRENT_VERSIONDEFAULT 1 NOT NULL,VARCHAR2(100),NUMBERVERSI

4、ON_HISTORYFAULT init input NOT NULL,VARCHAR2(3000)DEDEVELOP_DATEDATEDEFAULT SYSDATE NOT NULL,DEVELOP_BYVARCHAR2(100)DEFAULT cyyanisoftstone NOT NULL,LAST_MAINTAIN_DATEDATEDEFAULT SYSDATE NOT NULL,DELAST_MAINTAIN_BY VARCHAR2(100)FAULT cyyanisoftstone NOT NULL,MEMOVARCHAR2(500),STATUSNUMBERDEFAULT 1,C

5、ONSTRAINT PK_ETL_TABLES PRIMARY KEY (ID);COMMENT ON TABLE ETL_TABLES IS此表用于維護(hù)ETL涉及到所有表，包括:db-業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫ods-操作數(shù)據(jù)數(shù)據(jù)庫dw-數(shù)據(jù)倉庫；*/* /* Table: ETL_VIEWS */*/*CREATE TABLE ETL_VIEWS(IDNUMBERDEFAULT -1 NOT NULL,VIEW_NAME NOT NULL,VIEW_TYPE NOT NULL,VIEW_ROOT_INVIEW_SELECTNOT NULL,VIEW_FROM NOT NULL,VIEW_WHEREVA

6、RCHAR2(100)VARCHAR2(30)VARCHAR2(30),VARCHAR2(4000)VARCHAR2(600)VARCHAR2(2000),VIEWORDERBYVARCHAR2(600),VIEW_GROUP_BYVARCHAR2(600),VIEW_HAVINGVARCHAR2(600),VIEW_DB_LINK_NAME VARCHAR2(100),CURRENT_VERSION NUMBERDEFAULT 1 NOT NULL,DEVERSION_HISTORY VARCHAR2(3000)FAULT init input NOT NULL,DEVELOP_DATEDA

7、TEDEFAULT SYSDATE NOT NULL,DEVELOP_BYVARCHAR2(100)DEFAULT cyyanisoftstone NOT NULL,LAST_MAINTAIN_DATEDATEDEFAULT SYSDATE NOT NULL,LAST_MAINTAIN_BY VARCHAR2(100)DEFAULT cyyanisoftstone NOT NULL,MEMOVARCHAR2(500),STATUSNUMBERDEFAULT 1,CONSTRAINT PK_ETL_VIEWS PRIMARY KEY (ID);COMMENT ON TABLE ETL_VIEWS

8、 IS此表用于維護(hù)ETL涉及到所有視圖,包括:v1-db 表中與ods對應(yīng)到視圖v2-ods表中與 db 對應(yīng)到視圖v3-ods 表中與dw對應(yīng)到視圖v4-dw 表中與 ods 中對應(yīng)到視圖 ;/*/* Table:ETLS*/*/CREATE TABLE ETLS (IDNUMBERNOT NULL,ETL_NAMEVARCHAR2(300)NOT NULL,ETL_TYPEVARCHAR2(30)NOT NULL,ETL_SRC_VIEW_OR_TABLENUMBERNOT NULL,ETL_DES_VIEW_OR_TABLENUMBERNOT NULL,ETL_INIT_ENABLENU

9、MBER(1)DEFAULT 1 NOT NULL,ETL_ADD_ENABLENUMBER(1)DEFAULT 1 NOT NULL,ETL_CHARGE_ENABLE NUMBER(1)DEFAULT 1 NOT NULL,CURRENT_VERSION NUMBERDEFAULT 1 NOT NULL,VERSION_HISTORY FAULT init input NOT NULL,VARCHAR2(3000)DEDEVELOP_DATEDATEDEFAULT SYSDATE NOT NULL,DEVELOP_BYVARCHAR2（100）DEFAULT cyyanisoftstone

10、 NOT NULL,LAST_MAINTAIN_DATEDATEDEFAULT SYSDATE NOT NULL,LAST_MAINTAIN_BY VARCHAR2（100）DEFAULT cyyanisoftstone NOT NULL,MEMOVARCHAR2（500）,STATUSNUMBERDEFAULT 1,CONSTRAINT PK_ETLS PRIMARY KEY （ID）;COMMENT ON TABLE ETLS IS此表用于維護(hù)ETL轉(zhuǎn)換時(shí)設(shè)計(jì)到源表和目的表源表 （ 或視圖）- 目的表 （或視圖 ）（ 推薦全部使用視圖 , 視圖具有更過到靈活性, 而且更統(tǒng)一）整體架構(gòu)是在完

11、全相同兩張表（ 或視圖） 之間進(jìn)行同步處理規(guī)范 :源表 （ 或視圖 ）- 和目的表 （ 或視圖）- 完全相同目的視圖必須是單表;- 存儲(chǔ)過程*/*= /* Database name: %DATABASE% */* DBMS name:ORACLE Version10g*/* Created on:2009-2-123:29:27*/*/*-INTEGRITY PACKAGE DECLARATIONCREATE OR REPLACE PACKAGE INTEGRITYPACKAGE ASPROCEDURE INITNESTLEVEL;FUNCTION GETNESTLEVEL RETURN N

12、UMBER;PROCEDURE NEXTNESTLEVEL;PROCEDURE PREVIOUSNESTLEVEL;END INTEGRITYPACKAGE;/-INTEGRITY PACKAGE DEFINITIONCREATE OR REPLACE PACKAGE BODY INTEGRITYPACKAGE AS NESTLEVEL NUMBER;-PROCEDURE TO INITIALIZE THE TRIGGER NEST LEVEL PROCEDURE INITNESTLEVEL ISBEGINNESTLEVEL := 0;END;-FUNCTION TO RETURN THE T

13、RIGGER NEST LEVEL FUNCTION GETNESTLEVEL RETURN NUMBER IS BEGINIF NESTLEVEL IS NULL THEN NESTLEVEL := 0;END IF;RETURN(NESTLEVEL);END;-PROCEDURE TO INCREASE THE TRIGGER NEST LEVEL PROCEDURE NEXTNESTLEVEL ISBEGINIF NESTLEVEL IS NULL THENNESTLEVEL := 0;END IF;NESTLEVEL := NESTLEVEL + 1;END;- PROCEDURE T

14、O DECREASE THE TRIGGER NEST LEVEL PROCEDURE PREVIOUSNESTLEVEL ISBEGINNESTLEVEL := NESTLEVEL - 1;END;END INTEGRITYPACKAGE;/CREATE OR REPLACE PROCEDURE PRO_CREATE_VIEW_BY_ETL_VIEWS ASPRO_CREATE_VIEW_BY_ETL_VIEWS- CREATED ON 2009-2-1 BY CYYANISOFTSTONE-功能：根據(jù)ETL_VIEW外到數(shù)據(jù)生成視圖VIEW_CREATE_CODE VARCHAR2(100

15、00)fc 成視圖至欣碼VIEW_NAME VARCHAR2(100); - 視圖名稱VIEW_SELECT VARCHAR2(4000);-視圖的 SELECT?分VIEW_FROM VARCHAR2(300);-視圖的 FROM?分VIEW_WHERE VARCHAR2(3000);-視圖的 WHER部分VIEW_ORDER_BY VARCHAR2(600);-視圖的 ORDER BYB分VIEW_GROUP_BY VARCHAR2(600);-視圖的 GROUP BY分VIEW_HAVING VARCHAR2(600);-視圖的 HAVING分VIEW_DB_LINK_NAME VARC

16、HAR2(100);-視圖的 DB LINK部分ROW_COUNT NUMBERS數(shù)CURSOR ETL_VIEWS_CURSOR I-S- 提取創(chuàng)建視圖需要到信息SELECT VIEW_NAME, VIEW_SELECT, VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME FROM ETL_VIEWS T WHERE T.CURRENT_VERSION = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VI

17、EW_NAME = T2.VIEW_NAME);BEGIN- 統(tǒng)計(jì)行數(shù)SELECT COUNT(*) INTO ROW_COUNTFROM ETL_VIEWS T WHERE T.CURRENT_VERSION = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VIEW_NAME = T2.VIEW_NAME);OPEN ETL_VIEWS_CURSORHF 游標(biāo)FOR I IN 1 . ROW_COUNT LOOP - 遍歷FETCH ETL_VIEWS_CURSORINTO VIEW_NAME, VIEW_SELECT,

18、 VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME;- 拼接創(chuàng)建視圖到語句VIEW_CREATE_CODE := create or replace view | VIEW_NAME | as select | VIEW_SELECT | from | VIEW_FROM;IF VIEW_DB_LINK_NAME IS NOT NULL THENVIEW_CREATE_CODE := VIEW_CREATE_CODE | | VIEW_DB_LINK_NAME;END IF;I

19、F VIEW_WHERE IS NOT NULL THENIEW_CREATE_CODE := VIEW_CREATE_CODE | where | VIEW_WHERE;END IF;IF VIEW_ORDER_BY IS NOT NULL THENIEW_CREATE_CODE := VIEW_CREATE_CODE | order by | VIEW_ORDER_BY;END IF;IF VIEW_GROUP_BY IS NOT NULL THENIEW_CREATE_CODE := VIEW_CREATE_CODE | group by | VIEW_GROUP_BY;END IF;I

20、F VIEWHAVING IS NOT NULL THENVIEW_CREATE_CODE := VIEW_CREATE_CODE | having | VIEW_HAVING;END IF;- 輸出創(chuàng)建語句-DBMS_OUTPUT.PUT_LINE(VIEW_CREATE_CODE);-DBMS_OUTPUT.PUT_LINE();- 執(zhí)行創(chuàng)建視圖EXECUTE IMMEDIATE VIEW_CREATE_CODE;END LOOP;CLOSE ETL_VIEWS_CURSOR閉游標(biāo)END;/CREATE OR REPLACE PROCEDURE PRO_INSERT_INTO_ETL_VI

21、EWS AS-ADD BY CYYANISOFTSTONE-2009 年 2 月 1 日 21:33:37-此存儲(chǔ)過程用于 將ETL_TABL4標(biāo)識(shí)需要?jiǎng)?chuàng)建VIEW到TABLE,進(jìn)行自動(dòng)提 起轉(zhuǎn)換到ETL_VIEW舟.-處理過程用百啦系統(tǒng)表COLR此表中獲取列名TABLE_NAME VARCHAR2(100); 表名-COL_NAME VARCHAR2(100); - 列名TABLE_COUNT NUMBER至 U行數(shù)-COL_COUNT NUMBER; -列數(shù)ETL_VIEWS_INSERT_CODE VARCHAR2(60硼八語句至U INSERT部分ETL_VIEWS_VALUES_CO

22、DE VARCHAR2(16000)；入語句至U VALUE都分-ETL_VIEWS 的至歷 UVIEW_NAME_PREFIX VARCHAR2(30)-體名至 U 前綴TABLE_TYPE VARCHAR2(30); -表類型-如 DB, ODS, DWTABLE_ROOT_IN VARCHAR2(30); 表來源-, 來自那個(gè)系統(tǒng), 如資金系統(tǒng)NHZJ, 財(cái)務(wù)系統(tǒng) NHCWVIEW_SELECT VARCHAR2(10000);-VIEWI旬至U SELECT?分，這個(gè)需要遍 歷一個(gè)表到所有列DB_LINK_NAME VARCHAR2(100);CURRENT_VERSION VARCH

23、AR2(600)M部分,這里沒更新,只要全部刪 除 , 或不斷插入 , 此字段定義了版本, 沒有變更都形成新到版本, 取值是取最大值CURSOR_NUMBER NUMBER;COL_SELECT_SQL VARCHAR2(100);RETURN_VALUE NUMBER;-從ETL_TABLE竽查詢需要生成視圖到表CURSOR DB_TABLES_CURSOR ISSELECT UPPER(TABLE_NAME), T.TABLE_TYPE, T.TABLE_ROOT_IN, T.TABLE_CREATE_VIEW_NAME_PREFIX, DB_LINK_NAME FROM ETL_TABL

24、ES T WHERE (UPPER(T.TABLE_TYPE) = DB OR UPPER(T.TABLE_TYPE) = DW ) AND T.TABLE_NEED_CREATE_VIEW = 1;-CURSOR_NUMBER NUMBER；標(biāo) OLD WAY執(zhí)行需要,NEW WAY需要-RETURN_VALUE NUMBER擲亍后返回值OLD WAY行需要,NEW WAY不需要BEGIN- TEST STATEMENTS HERESELECT COUNT(*) INTO TABLE_COUNT FROM ETL_TABLES T WHERE (UPPER(T.TABLE_TYPE) = D

25、B OR UPPER(T.TABLE_TYPE) = DW ) AND T.TABLE_NEED_CREATE_VIEW = 1;-構(gòu)造INSERTS分ETL_VIEWS_INSERT_CODE:= insert into etl_views(view_name, view_type, view_root_in, view_select, view_from, current_version, VIEW_DB_LINK_NAME) ;OPEN DB_TABLES_CURSOR;FOR I IN 1 . TABLE_COUNT LOOP - 表遍歷FETCH DB_TABLES_CURSORIN

26、TOTABLE_NAMET,ABLE_TYPET, ABLE_ROOT_INV,IEW_NAME_PREFIX, DB_LINK_NAME;-構(gòu)造VALUES?分ETL_VIEWS_VALUES_COD:E= values( | VIEW_NAME_PREF|IX TABLE_NAME | , |TABLE_TYPE | , | TABLE_ROOT_IN |Illi .DBMS_OUTPUT.PUT(TABLE_NAME);/* 使用CURSOR歷列到方法不適用于DB_LINK- 準(zhǔn)備遍歷列SELECT COUNT(*) INTO COL_COUNT FROM COLDB_LINK_NHZJ

27、 WHERE COL.TNAME = UPPER(TABLE_NAME);DBMS_OUTPUT.PUT_LINE( table has | COL_COUNT | cols);DECLARECURSOR COLS_CURSOR ISSELECT C.CNAME FROM COLDB_LINK_NHZJ C WHERE C.TNAME = UPPER(TABLE_NAME);BEGINOPEN COLS_CURSOR;VIEW_SELECT := ;- 下面用逗號拼接列FETCH COLS_CURSO旅歷第一歹 UINTO COL_NAME;VIEW_SELECT := VIEW_SELECT

28、 | COL_NAME;FOR J IN 2 . COL_COUNT LOOP -遍歷后面到列FETCH COLS_CURSORINTO COL_NAME;DBMS_OUTPUT.PUT_LINE( | COL_NAME);VIEW_SELECT := VIEW_SELECT | , | COL_NAME;END LOOP;CLOSE COLS_CURSOR;END;-DBMS_OUTPUT.PUT_LINE(VIEW_SELECT);- DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_VALUES_CODE);*/* 使用 DBMS_SQL */- ANOTHER WAY US

29、ER DBMS_SQL PACKAGECOL_SELECT_SQ:=L select ame from sys.col | DB_LINK_NAME| T where T.tname = | TABLE_NAME | ;-SQL_CODE := select ame from sys.col T where T.tname = | TABLE_NAME | ;CURSOR_NUMBER := DBMS_SQL.OPEN_CURSOR();DBMS_SQL.PARSE(CURSOR_NUMBER, COL_SELECT_SQL, DBMS_SQL.NATIVE);DBMS_SQL.DEFINE_

30、COLUMN(CURSOR_NUMBER,1,COL_NAME, 100);RETURN_VALUE := DBMS_SQL.EXECUTE(CURSOR_NUMBER);DBMS_OUTPUT.PUT_LINE( RETURN_VALUE = | RETURN_VALUE);RETURN_VALUE k DBMS_SQL.FETCH_ROWS(CURSOR_NUMBER).DBMS_SQL.COLUMN_VALUE(CURSOR_NUMBER,1,COL_NAME);VIEW_SELECT := COL_NAME;WHILE DBMS_SQL.FETCH_ROWS(CURSOR_NUMBER

31、)BLOOP 其它至 U 列DBMS_SQL.COLUMN_VALUE(CURSOR_NUMBER,1,COL_NAME);DBMS_OUTPUT.PUT_LINE(COL_NAME);VIEW_SELECT := VIEW_SELECT | , | COL_NAME;END LOOP;- DBMS_OUTPUT.PUT_LINE(VIEW_SELECT : | VIEW_SELECT);DBMS_SQL.CLOSE_CURSOR(CURSOR_NUMBER);- 生成最新到版本號: 視圖名稱是唯一的SELECTNVL(MAX(CURRENT_VERSION)+,01) INTO CURRENT_VERSIFORNOM ETL_VIEWS V WHERE V.VIEW_NAME = VIEW_NAME_PREFIX | TABLE_NAME;ETL_VIEWS_VALUES_COD:E= ETL_VIEWS_VALUES_CO|DECHR(10) | ,| VIEW_SELEC|T| | CHR(10) | , | TABLE_NAM|E| ,| CURRENT_VERSION | , | DB_LINK_NA