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'
升级会员 