Oracel存储过程通用ETL实现.docx

Oracel存储过程通用ETL实现.docx

《Oracel存储过程通用ETL实现.docx》由会员分享，可在线阅读，更多相关《Oracel存储过程通用ETL实现.docx（19页珍藏版）》请在冰豆网上搜索。

Oracel存储过程通用ETL实现

Oracel存储过程-通用ETL实现

数据规格化

处理自动化

信息集中化

操作人性化

架构

通过视图实现来至两个不同数据库的表的结构完全一致,  在结构完全相同的两个表之间进行数据同步,问题变得相当简单.同步代码如下.

ETL

---初始同步

deletefromods_table;

insertintov_table

select*fromdb_table;

commit;

---新增同步

insertintov_table

select*fromdb_tablet

wherenotin（selectidfromv_table）;

commit;

---变更同步

updateods_tablet

set=（selectfromdb_tabledbwhere=

where!

=（selectfromdb_tabledbwhere=;

commit;

实现两个表结构完全一致的方法如下

---建表

CREATESEQUENCESEQ_ETL_INCREASE_ID

INCREMENTBY1

STARTWITH1

  NOCACHE;

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

/*Table:

ETL_TABLES                              */

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

CREATETABLEETL_TABLES  （

  "ID"            NUMBER                DEFAULT-1NOTNULL,

  "TABLE_NAME"      VARCHAR2（100）            NOTNULL,

  "TABLE_TYPE"      VARCHAR2（30）              NOTNULL,

  "TABLE_ROOT_IN"    VARCHAR2（30）,

  "TABLE_NEED_CREATE_VIEW"NUMBER                DEFAULT1,

  "TABLE_CREATE_VIEW_NAME_PREFIX"VARCHAR2（30）            DEFAULT'v',

  "DB_LINK_NAME"    VARCHAR2（100）,

  "CURRENT_VERSION"  NUMBER                DEFAULT1NOTNULL,

  "VERSION_HISTORY"  VARCHAR2（3000）            DEFAULT'initinput'NOTNULL,

  "DEVELOP_DATE"    DATE                  DEFAULTSYSDATENOTNULL,

  "DEVELOP_BY"      VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "LAST_MAINTAIN_DATE"DATE                  DEFAULTSYSDATENOTNULL,

  "LAST_MAINTAIN_BY"  VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "MEMO"          VARCHAR2（500）,

  "STATUS"        NUMBER                DEFAULT1,

  CONSTRAINTPK_ETL_TABLESPRIMARYKEY（"ID"）

）;

COMMENTONTABLEETL_TABLESIS

'此表用于维护ETL涉及到所有表,包括:

1,db---业务系统数据库

2,ods---操作数据数据库

3,dw---数据仓库';

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

/*Table:

ETL_VIEWS                              */

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

CREATETABLEETL_VIEWS  （

  "ID"            NUMBER                DEFAULT-1NOTNULL,

  "VIEW_NAME"      VARCHAR2（100）            NOTNULL,

  "VIEW_TYPE"      VARCHAR2（30）              NOTNULL,

  "VIEW_ROOT_IN"    VARCHAR2（30）,

  "VIEW_SELECT"      VARCHAR2（4000）            NOTNULL,

  "VIEW_FROM"      VARCHAR2（600）            NOTNULL,

  "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                DEFAULT1NOTNULL,

  "VERSION_HISTORY"  VARCHAR2（3000）            DEFAULT'initinput'NOTNULL,

  "DEVELOP_DATE"    DATE                  DEFAULTSYSDATENOTNULL,

  "DEVELOP_BY"      VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "LAST_MAINTAIN_DATE"DATE                  DEFAULTSYSDATENOTNULL,

  "LAST_MAINTAIN_BY"  VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "MEMO"          VARCHAR2（500）,

  "STATUS"        NUMBER                DEFAULT1,

  CONSTRAINTPK_ETL_VIEWSPRIMARYKEY（"ID"）

）;

COMMENTONTABLEETL_VIEWSIS

'此表用于维护ETL涉及到所有视图,包括:

1,v1---db表中与ods对应到视图

2,v2---ods表中与db对应到视图

3,v3---ods表中与dw对应到视图

4,v4---dw表中与ods中对应到视图';

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

/*Table:

ETLS                                  */

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

CREATETABLEETLS  （

  "ID"            NUMBER                  NOTNULL,

  "ETL_NAME"        VARCHAR2（300）            NOTNULL,

  "ETL_TYPE"        VARCHAR2（30）              NOTNULL,

  "ETL_SRC_VIEW_OR_TABLE"NUMBER                  NOTNULL,

  "ETL_DES_VIEW_OR_TABLE"NUMBER                  NOTNULL,

  "ETL_INIT_ENABLE"  NUMBER

（1）              DEFAULT1NOTNULL,

  "ETL_ADD_ENABLE"    NUMBER

（1）              DEFAULT1NOTNULL,

  "ETL_CHARGE_ENABLE"  NUMBER

（1）              DEFAULT1NOTNULL,

  "CURRENT_VERSION"  NUMBER                DEFAULT1NOTNULL,

  "VERSION_HISTORY"  VARCHAR2（3000）            DEFAULT'initinput'NOTNULL,

  "DEVELOP_DATE"    DATE                  DEFAULTSYSDATENOTNULL,

  "DEVELOP_BY"      VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "LAST_MAINTAIN_DATE"DATE                  DEFAULTSYSDATENOTNULL,

  "LAST_MAINTAIN_BY"  VARCHAR2（100）            DEFAULT'cyyan@isoftstone'NOTNULL,

  "MEMO"          VARCHAR2（500）,

  "STATUS"        NUMBER                DEFAULT1,

  CONSTRAINTPK_ETLSPRIMARYKEY（"ID"）

）;

COMMENTONTABLEETLSIS

'此表用于维护ETL转换时设计到源表和目的表

源表（或视图）--->目的表（或视图）-

（推荐全部使用视图,视图具有更过到灵活性,而且更统一）

整体架构是在完全相同两张表（或视图）之间进行同步处理

规范:

1,  源表（或视图）-和目的表（或视图）-完全相同

2,  目的视图必须是单表';

--存储过程

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

/*Databasename:

  %DATABASE%                        */

/*DBMSname:

    ORACLEVersion10g                  */

/*Createdon:

    2009-2-123:

29:

27                  */

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

--INTEGRITYPACKAGEDECLARATION

CREATEORREPLACEPACKAGEINTEGRITYPACKAGEAS

PROCEDUREINITNESTLEVEL;

FUNCTIONGETNESTLEVELRETURNNUMBER;

PROCEDURENEXTNESTLEVEL;

PROCEDUREPREVIOUSNESTLEVEL;

ENDINTEGRITYPACKAGE;

/

--INTEGRITYPACKAGEDEFINITION

CREATEORREPLACEPACKAGEBODYINTEGRITYPACKAGEAS

NESTLEVELNUMBER;

--PROCEDURETOINITIALIZETHETRIGGERNESTLEVEL

PROCEDUREINITNESTLEVELIS

BEGIN

NESTLEVEL:

=0;

END;

--FUNCTIONTORETURNTHETRIGGERNESTLEVEL

FUNCTIONGETNESTLEVELRETURNNUMBERIS

BEGIN

IFNESTLEVELISNULLTHEN

    NESTLEVEL:

=0;

ENDIF;

RETURN（NESTLEVEL）;

END;

--PROCEDURETOINCREASETHETRIGGERNESTLEVEL

PROCEDURENEXTNESTLEVELIS

BEGIN

IFNESTLEVELISNULLTHEN

    NESTLEVEL:

=0;

ENDIF;

NESTLEVEL:

=NESTLEVEL+1;

END;

--PROCEDURETODECREASETHETRIGGERNESTLEVEL

PROCEDUREPREVIOUSNESTLEVELIS

BEGIN

NESTLEVEL:

=NESTLEVEL-1;

END;

ENDINTEGRITYPACKAGE;

/

CREATEORREPLACEPROCEDUREPRO_CREATE_VIEW_BY_ETL_VIEWS

AS

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

--CREATEDON2009-2-1BYCYYAN@ISOFTSTONE

--功能:

根据ETL_VIEWS中到数据生成视图

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

  VIEW_CREATE_CODEVARCHAR2（10000）;--生成视图到代码

  VIEW_NAMEVARCHAR2（100）;  --视图名称

  VIEW_SELECTVARCHAR2（4000）;  --视图的SELECT部分

  VIEW_FROMVARCHAR2（300）;  --视图的FROM部分

  VIEW_WHEREVARCHAR2（3000）;  --视图的WHERE部分

  VIEW_ORDER_BYVARCHAR2（600）;  --视图的ORDERBY部分

  VIEW_GROUP_BYVARCHAR2（600）;  --视图的GROUPBY部分

  VIEW_HAVINGVARCHAR2（600）;  --视图的HAVING部分

  VIEW_DB_LINK_NAMEVARCHAR2（100）;  --视图的DBLINK部分

  ROW_COUNTNUMBER;--行数

  CURSORETL_VIEWS_CURSORIS  --提取创建视图需要到信息

  SELECTVIEW_NAME,VIEW_SELECT,VIEW_FROM,VIEW_WHERE,VIEW_ORDER_BY,VIEW_GROUP_BY,VIEW_HAVING,VIEW_DB_LINK_NAMEFROMETL_VIEWSTWHERE=（SELECTMAXFROMETL_VIEWST2WHERE=;

BEGIN

  --统计行数

  SELECTCOUNT（*）INTOROW_COUNT  FROMETL_VIEWSTWHERE=（SELECTMAXFROMETL_VIEWST2WHERE=;

  OPENETL_VIEWS_CURSOR;--打开游标

  FORIIN1..ROW_COUNT  LOOP  --遍历

  FETCHETL_VIEWS_CURSOR

    INTOVIEW_NAME,VIEW_SELECT,VIEW_FROM,VIEW_WHERE,VIEW_ORDER_BY,VIEW_GROUP_BY,VIEW_HAVING,VIEW_DB_LINK_NAME;

    ---拼接创建视图到语句

  VIEW_CREATE_CODE:

='createorreplaceview'||VIEW_NAME||'asselect'||VIEW_SELECT||'from'||VIEW_FROM;

  IFVIEW_DB_LINK_NAMEISNOTNULLTHEN

    VIEW_CREATE_CODE:

=VIEW_CREATE_CODE||'@'||VIEW_DB_LINK_NAME;

  ENDIF;

  IFVIEW_WHEREISNOTNULLTHEN

    VIEW_CREATE_CODE:

=VIEW_CREATE_CODE||'where'||VIEW_WHERE;

  ENDIF;

  IFVIEW_ORDER_BYISNOTNULLTHEN

    VIEW_CREATE_CODE:

=VIEW_CREATE_CODE||'orderby'||VIEW_ORDER_BY;

  ENDIF;

  IFVIEW_GROUP_BYISNOTNULLTHEN

    VIEW_CREATE_CODE:

=VIEW_CREATE_CODE||'groupby'||VIEW_GROUP_BY;

  ENDIF;

  IFVIEW_HAVINGISNOTNULLTHEN

    VIEW_CREATE_CODE:

=VIEW_CREATE_CODE||'having'||VIEW_HAVING;

  ENDIF;

  --输出创建语句

  （VIEW_CREATE_CODE）;

  （''）;

  --执行创建视图

  EXECUTEIMMEDIATEVIEW_CREATE_CODE;

  ENDLOOP;

  CLOSEETL_VIEWS_CURSOR;--关闭游标

END;

/

CREATEORREPLACEPROCEDUREPRO_INSERT_INTO_ETL_VIEWS

AS

--ADDBYCYYAN@ISOFTSTONE

--2009年2月1日21:

33:

37

---此存储过程用于将ETL_TABLE中标识需要创建VIEW到TABLE,进行自动提起转换到ETL_VIEWS中.

--处理过程用到啦系统表COL从此表中获取列名

  TABLE_NAMEVARCHAR2（100）;--表名

  COL_NAME  VARCHAR2（100）;--列名

  TABLE_COUNTNUMBER;--表到行数

  --COL_COUNT  NUMBER;--列数

  ETL_VIEWS_INSERT_CODEVARCHAR2（600）;--插入语句到INSERT部分

  ETL_VIEWS_VALUES_CODEVARCHAR2（16000）;--插入语句到VALUES部分

  --ETL_VIEWS的到列

  VIEW_NAME_PREFIXVARCHAR2（30）;--实体名到前缀

  TABLE_TYPEVARCHAR2（30）;--表类型如DB,ODS,DW

  TABLE_ROOT_INVARCHAR2（30）;--表来源,来自那个系统,如资金系统"NHZJ",财务系统"NHCW"

  VIEW_SELECTVARCHAR2（10000）;--VIEW语句到SELECT部分,这个需要遍历一个表到所有列

  DB_LINK_NAMEVARCHAR2（100）;

  CURRENT_VERSIONVARCHAR2（600）;--版本部分,这里没更新,只要全部删除,或不断插入,此字段定义了版本,没有变更都形成新到版本,取值是取最大值

  CURSOR_NUMBERNUMBER;

  COL_SELECT_SQLVARCHAR2（100）;

  RETURN_VALUENUMBER;

  --从ETL_TABLES中查询需要生成视图到表

  CURSORDB_TABLES_CURSORIS

  SELECTUPPER（TABLE_NAME）,,,,DB_LINK_NAMEFROMETL_TABLESTWHERE（UPPER='DB'