亚洲另类无码专区丝袜,亚洲第一页在线观看,亚洲美国产亚洲AV

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

轉(zhuǎn)自：http://www.javaeye.com/topic/321719

方案理念--四化理念

數(shù)據(jù)規(guī)格化
處理自動化
信息集中化
操作人性化

架構(gòu)

通過視圖實現(xiàn)來至兩個不同數(shù)據(jù)庫的表的結(jié)構(gòu)完全一致, 在結(jié)構(gòu)完全相同的兩個表之間進(jìn)行數(shù)據(jù)同步, 問題變得相當(dāng)簡單. 同步代碼如下.

ETL

---初始同步

delete from ods_table;

insert into v_table

select * from db_table;

commit;

---新增同步

insert into v_table

select * from db_table t

where t.id not in (select id from v_table);

commit;

---變更同步

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;

實現(xiàn)兩個表結(jié)構(gòu)完全一致的方法如下

---建表

CREATE SEQUENCE SEQ_ETL_INCREASE_ID

INCREMENT BY 1

START WITH 1

NOCACHE;

/*==============================================================*/

/* Table: ETL_TABLES */

/*==============================================================*/

CREATE TABLE ETL_TABLES (

"ID" NUMBER DEFAULT -1 NOT NULL,

"TABLE_NAME" VARCHAR2(100) NOT NULL,

"TABLE_TYPE" VARCHAR2(30) NOT NULL,

"TABLE_ROOT_IN" VARCHAR2(30),

"TABLE_NEED_CREATE_VIEW" NUMBER DEFAULT 1,

"TABLE_CREATE_VIEW_NAME_PREFIX" VARCHAR2(30) DEFAULT 'v',

"DB_LINK_NAME" VARCHAR2(100),

"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,

"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,

"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,

"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,

"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"MEMO" VARCHAR2(500),

"STATUS" NUMBER DEFAULT 1,

CONSTRAINT PK_ETL_TABLES PRIMARY KEY ("ID")

);

COMMENT ON TABLE ETL_TABLES IS

'此表用于維護(hù)ETL涉及到所有表, 包括:

1, db---業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫

2, ods---操作數(shù)據(jù)數(shù)據(jù)庫

3, dw---數(shù)據(jù)倉庫';

/*==============================================================*/

/* Table: ETL_VIEWS */

/*==============================================================*/

CREATE TABLE ETL_VIEWS (

"ID" NUMBER DEFAULT -1 NOT NULL,

"VIEW_NAME" VARCHAR2(100) NOT NULL,

"VIEW_TYPE" VARCHAR2(30) NOT NULL,

"VIEW_ROOT_IN" VARCHAR2(30),

"VIEW_SELECT" VARCHAR2(4000) NOT NULL,

"VIEW_FROM" VARCHAR2(600) NOT NULL,

"VIEW_WHERE" VARCHAR2(2000),

"VIEW_ORDER_BY" VARCHAR2(600),

"VIEW_GROUP_BY" VARCHAR2(600),

"VIEW_HAVING" VARCHAR2(600),

"VIEW_DB_LINK_NAME" VARCHAR2(100),

"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,

"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,

"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,

"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,

"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"MEMO" VARCHAR2(500),

"STATUS" NUMBER DEFAULT 1,

CONSTRAINT PK_ETL_VIEWS PRIMARY KEY ("ID")

);

COMMENT ON TABLE ETL_VIEWS IS

'此表用于維護(hù)ETL涉及到所有視圖, 包括:

1, v1---db表中與ods對應(yīng)到視圖

2, v2---ods表中與db對應(yīng)到視圖

3, v3---ods表中與dw對應(yīng)到視圖

4, v4---dw表中與ods中對應(yīng)到視圖';

/*==============================================================*/

/* Table: ETLS */

/*==============================================================*/

CREATE TABLE ETLS (

"ID" NUMBER NOT NULL,

"ETL_NAME" VARCHAR2(300) NOT NULL,

"ETL_TYPE" VARCHAR2(30) NOT NULL,

"ETL_SRC_VIEW_OR_TABLE" NUMBER NOT NULL,

"ETL_DES_VIEW_OR_TABLE" NUMBER NOT NULL,

"ETL_INIT_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,

"ETL_ADD_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,

"ETL_CHARGE_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,

"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,

"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,

"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,

"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,

"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,

"MEMO" VARCHAR2(500),

"STATUS" NUMBER DEFAULT 1,

CONSTRAINT PK_ETLS PRIMARY KEY ("ID")

);

COMMENT ON TABLE ETLS IS

'此表用于維護(hù)ETL轉(zhuǎn)換時設(shè)計到源表和目的表

源表(或視圖)--->目的表(或視圖)-

(推薦全部使用視圖, 視圖具有更過到靈活性, 而且更統(tǒng)一)

整體架構(gòu)是在完全相同兩張表(或視圖)之間進(jìn)行同步處理

規(guī)范:

1, 源表(或視圖)-和目的表(或視圖)-完全相同

2, 目的視圖必須是單表';

--存儲過程

/*==============================================================*/

/* Database name: %DATABASE% */

/* DBMS name: ORACLE Version 10g */

/* Created on: 2009-2-1 23:29:27 */

/*==============================================================*/

-- INTEGRITY PACKAGE DECLARATION

CREATE OR REPLACE PACKAGE INTEGRITYPACKAGE AS

PROCEDURE INITNESTLEVEL;

FUNCTION GETNESTLEVEL RETURN NUMBER;

PROCEDURE NEXTNESTLEVEL;

PROCEDURE PREVIOUSNESTLEVEL;

END INTEGRITYPACKAGE;

-- INTEGRITY PACKAGE DEFINITION

CREATE OR REPLACE PACKAGE BODY INTEGRITYPACKAGE AS

NESTLEVEL NUMBER;

-- PROCEDURE TO INITIALIZE THE TRIGGER NEST LEVEL

PROCEDURE INITNESTLEVEL IS

BEGIN

NESTLEVEL := 0;

END;

-- FUNCTION TO RETURN THE TRIGGER NEST LEVEL

FUNCTION GETNESTLEVEL RETURN NUMBER IS

BEGIN

IF NESTLEVEL IS NULL THEN

NESTLEVEL := 0;

END IF;

RETURN(NESTLEVEL);

END;

-- PROCEDURE TO INCREASE THE TRIGGER NEST LEVEL

PROCEDURE NEXTNESTLEVEL IS

BEGIN

IF NESTLEVEL IS NULL THEN

NESTLEVEL := 0;

END IF;

NESTLEVEL := NESTLEVEL + 1;

END;

-- PROCEDURE TO DECREASE THE TRIGGER NEST LEVEL

PROCEDURE PREVIOUSNESTLEVEL IS

BEGIN

NESTLEVEL := NESTLEVEL - 1;

END;

END INTEGRITYPACKAGE;

CREATE OR REPLACE PROCEDURE PRO_CREATE_VIEW_BY_ETL_VIEWS

--------------PRO_CREATE_VIEW_BY_ETL_VIEWS------------------------

-- CREATED ON 2009-2-1 BY CYYAN@ISOFTSTONE

-- 功能 : 根據(jù)ETL_VIEWS中到數(shù)據(jù)生成視圖

------------------------------------------------------------------------------

VIEW_CREATE_CODE VARCHAR2(10000); --生成視圖到代碼

VIEW_NAME VARCHAR2(100); --視圖名稱

VIEW_SELECT VARCHAR2(4000); --視圖的SELECT部分

VIEW_FROM VARCHAR2(300); --視圖的FROM部分

VIEW_WHERE VARCHAR2(3000); --視圖的WHERE部分

VIEW_ORDER_BY VARCHAR2(600); --視圖的ORDER BY部分

VIEW_GROUP_BY VARCHAR2(600); --視圖的GROUP BY部分

VIEW_HAVING VARCHAR2(600); --視圖的HAVING部分

VIEW_DB_LINK_NAME VARCHAR2(100); --視圖的DB LINK部分

ROW_COUNT NUMBER; --行數(shù)

CURSOR ETL_VIEWS_CURSOR IS --提取創(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.VIEW_NAME = T2.VIEW_NAME);

BEGIN

-- 統(tǒng)計行數(shù)

SELECT COUNT(*) INTO ROW_COUNT FROM 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_CURSOR; --打開游標(biāo)

FOR I IN 1 .. ROW_COUNT LOOP --遍歷

FETCH ETL_VIEWS_CURSOR

INTO VIEW_NAME, VIEW_SELECT, 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 THEN

VIEW_CREATE_CODE := VIEW_CREATE_CODE || '@' || VIEW_DB_LINK_NAME;

END IF;

IF VIEW_WHERE IS NOT NULL THEN

VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' where ' || VIEW_WHERE;

END IF;

IF VIEW_ORDER_BY IS NOT NULL THEN

VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' order by ' || VIEW_ORDER_BY;

END IF;

IF VIEW_GROUP_BY IS NOT NULL THEN

VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' group by ' || VIEW_GROUP_BY;

END IF;

IF VIEW_HAVING IS NOT NULL THEN

VIEW_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; --關(guān)閉游標(biāo)

END;

CREATE OR REPLACE PROCEDURE PRO_INSERT_INTO_ETL_VIEWS

--ADD BY CYYAN@ISOFTSTONE

--2009年2月1日21:33:37

---此存儲過程用于將ETL_TABLE中標(biāo)識需要創(chuàng)建VIEW 到TABLE, 進(jìn)行自動提起轉(zhuǎn)換到ETL_VIEWS中.

--處理過程用到啦系統(tǒng)表COL從此表中獲取列名

TABLE_NAME VARCHAR2(100); --表名

COL_NAME VARCHAR2(100); --列名

TABLE_COUNT NUMBER; --表到行數(shù)

--COL_COUNT NUMBER; --列數(shù)

ETL_VIEWS_INSERT_CODE VARCHAR2(600); --插入語句到 INSERT部分

ETL_VIEWS_VALUES_CODE VARCHAR2(16000); --插入語句到VALUES部分

--ETL_VIEWS的到列

VIEW_NAME_PREFIX VARCHAR2(30);--實體名到前綴

TABLE_TYPE VARCHAR2(30); --表類型如 DB, ODS, DW

TABLE_ROOT_IN VARCHAR2(30); --表來源, 來自那個系統(tǒng), 如資金系統(tǒng)"NHZJ", 財務(wù)系統(tǒng)"NHCW"

VIEW_SELECT VARCHAR2(10000); --VIEW 語句到SELECT部分, 這個需要遍歷一個表到所有列

DB_LINK_NAME VARCHAR2(100);

CURRENT_VERSION VARCHAR2(600); --版本部分, 這里沒更新, 只要全部刪除, 或不斷插入, 此字段定義了版本, 沒有變更都形成新到版本, 取值是取最大值

CURSOR_NUMBER NUMBER;

COL_SELECT_SQL VARCHAR2(100);

RETURN_VALUE NUMBER;

--從ETL_TABLES中查詢需要生成視圖到表

CURSOR DB_TABLES_CURSOR IS

SELECT UPPER(TABLE_NAME), T.TABLE_TYPE, T.TABLE_ROOT_IN, T.TABLE_CREATE_VIEW_NAME_PREFIX, DB_LINK_NAME FROM ETL_TABLES 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; --執(zhí)行后返回值 OLD WAY 執(zhí)行需要, NEW WAY 不需要

BEGIN

-- TEST STATEMENTS HERE

SELECT COUNT(*) INTO TABLE_COUNT FROM ETL_TABLES T WHERE (UPPER(T.TABLE_TYPE) = 'DB' OR UPPER(T.TABLE_TYPE) = 'DW' ) AND T.TABLE_NEED_CREATE_VIEW = 1;

--構(gòu)造INSERT部分

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_CURSOR

INTO TABLE_NAME, TABLE_TYPE, TABLE_ROOT_IN, VIEW_NAME_PREFIX, DB_LINK_NAME;

--構(gòu)造VALUES部分

ETL_VIEWS_VALUES_CODE := 'values(''' || VIEW_NAME_PREFIX || TABLE_NAME || ''', ''' || TABLE_TYPE || ''', ''' || TABLE_ROOT_IN || '''';

DBMS_OUTPUT.PUT(TABLE_NAME);

/* 使用CURSOR遍歷列到方法不適用于DB_LINK

--準(zhǔn)備遍歷列

SELECT COUNT(*) INTO COL_COUNT FROM COL@DB_LINK_NHZJ WHERE COL.TNAME = UPPER(TABLE_NAME);

DBMS_OUTPUT.PUT_LINE(' table has ' || COL_COUNT || ' cols');

DECLARE

CURSOR COLS_CURSOR IS

SELECT C.CNAME FROM COL@DB_LINK_NHZJ C WHERE C.TNAME = UPPER(TABLE_NAME);

BEGIN

OPEN COLS_CURSOR;

VIEW_SELECT := '';

--下面用逗號拼接列

FETCH COLS_CURSOR --遍歷第一列

INTO COL_NAME;

VIEW_SELECT := VIEW_SELECT || COL_NAME;

FOR J IN 2 .. COL_COUNT LOOP --遍歷后面到列

FETCH COLS_CURSOR

INTO 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 USER DBMS_SQL PACKAGE

COL_SELECT_SQL := 'select t.cname from sys.col@' || DB_LINK_NAME || ' T where T.tname = ''' || TABLE_NAME || '''';

--SQL_CODE := 'select t.cname 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_COLUMN(CURSOR_NUMBER,1,COL_NAME, 100);

RETURN_VALUE := DBMS_SQL.EXECUTE(CURSOR_NUMBER);

DBMS_OUTPUT.PUT_LINE(' RETURN_VALUE = ' || RETURN_VALUE);

RETURN_VALUE := 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)<>0 LOOP ---遍歷其它到列

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);

--生成最新到版本號: 視圖名稱是唯一的

SELECT NVL(MAX(CURRENT_VERSION),0) + 1 INTO CURRENT_VERSION FROM ETL_VIEWS V WHERE V.VIEW_NAME = VIEW_NAME_PREFIX || TABLE_NAME;

ETL_VIEWS_VALUES_CODE := ETL_VIEWS_VALUES_CODE || CHR(10) || ', ''' || VIEW_SELECT || '''' || CHR(10) || ', ''' || TABLE_NAME || ''', ''' || CURRENT_VERSION || ''', ''' || DB_LINK_NAME || ''')';

--輸出插入到語句

--DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_INSERT_CODE);

--DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_VALUES_CODE);

--DBMS_OUTPUT.PUT_LINE('');

--DBMS_STANDARD.

--執(zhí)行插入語句

-- NEW WAY

EXECUTE IMMEDIATE ETL_VIEWS_INSERT_CODE || ETL_VIEWS_VALUES_CODE;

-- OLD WAY

CURSOR_NUMBER := DBMS_SQL.OPEN_CURSOR();

DBMS_SQL.PARSE(CURSOR_NUMBER, ETL_VIEWS_INSERT_CODE, DBMS_SQL.NATIVE);

RETURN_VALUE := DBMS_SQL.EXECUTE(CURSOR_NUMBER);

DBMS_SQL.CLOSE_CURSOR(CURSOR_NUMBER);

END LOOP;

COMMIT; --提交

CLOSE DB_TABLES_CURSOR; --關(guān)閉游標(biāo)

--EXCEPTION

--ROLLBACK;

END;

CREATE OR REPLACE TRIGGER TRG_ID_ON_ETLS

BEFORE INSERT ON ETLS

FOR EACH ROW

DECLARE

-- NOTHING

BEGIN

SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;

END TRIGGER_ID_INCREASE;

CREATE OR REPLACE TRIGGER TRG_ID_ON_ETL_TABLES

BEFORE INSERT ON ETL_TABLES

FOR EACH ROW

DECLARE

-- NOTHING

BEGIN

SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;

END TRIGGER_ID_INCREASE;

CREATE OR REPLACE TRIGGER TRG_ID_ON_ETL_VIEWS

BEFORE INSERT ON ETL_VIEWS

FOR EACH ROW

DECLARE

-- NOTHING

BEGIN

SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;

END TRIGGER_ID_INCREASE;

posted on 2010-12-31 10:42 xzc 閱讀(1640) 評論(0) 編輯收藏所屬分類: Oracle

方案理念--四化理念

架構(gòu)

ETL

實現(xiàn)兩個表結(jié)構(gòu)完全一致的方法如下

---建表

--存儲過程

常用鏈接

留言簿(12)

隨筆分類

隨筆檔案

收藏夾

搜索

最新評論

閱讀排行榜

評論排行榜


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: Oracle to_char格式化函數(shù) 關(guān)于ociuldr ORACLE確定當(dāng)前記錄和下一條記錄之間的差值 SYS_CONNECT_BY_PATH函數(shù)用法 ORACLE Windows Oracle 10G手工創(chuàng)建數(shù)據(jù)庫談?wù)刉INDOWS下重裝系統(tǒng)后oracle的恢復(fù) DML、DDL、DCL區(qū)別 oracle 10g 正則表達(dá)式　REGEXP_LIKE Oracle 字符集的查看和修改 Oracle 10g Shrink Table 詳解［表空間收縮］