Informatica PowerCenter调用存储过程教学文案Word下载.docx
《Informatica PowerCenter调用存储过程教学文案Word下载.docx》由会员分享,可在线阅读,更多相关《Informatica PowerCenter调用存储过程教学文案Word下载.docx(19页珍藏版)》请在冰豆网上搜索。
安装路径
D:
\Informatica\9.6.1\services
域名:
Domain_GZ-ETL01
控制台访问地址:
http:
//10.33.2.235:
6008/administrator/
1.2BI数据库(10.240.3.2)
1.2.1软硬件配置
OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bit
工具
Pl/sql8.0.2.1505
2实现过程
本次示例使用组织部门层级无级树展开为横向层级关系,使用非连结存储过程调用。
步骤:
1)存储过程由组织部门层级无级树DW_DIM_DEPARTMENT表读出,调用定义递归函数GET_LONGDEPT、GET_LONGDEPT_CODE写入到横向展开临时表DW_DIM_DEPARTMENT_TEMP;
2)再将展开临时表DW_DIM_DEPARTMENT_TEMP的层级更新到DW_DIM_DEPARTMENT表;
3)于PowerCenter中当DW_DIM_DEPARTMENT由源表BD_DEPTDOC全表更新后,再调用SP_DW_DIM_DEPT_LONGDEPT实现;
2.1BI数据库创建存储过程
在目标数据库中创建并调试完成所需使用的存储过程,。
存储过程:
SP_DW_DIM_DEPT_LONGDEPT为例,结果如下:
2.1.1SP_DW_DIM_DEPT_LONGDEPT:
CREATEORREPLACEPROCEDURESP_DW_DIM_DEPT_LONGDEPTIS
BEGIN
--************************************************************************************************************
--名称:
SP_DW_DIM_DEPT_LONGDEPT
--功能:
将部门表组织层次无级转换为一行显示,部门名称、部门编码
--作者:
soutton
--创建日期:
2015-12-31
--调度描述:
暂无调度,提供给INFOMATICA的mapping:
m_DW_DIM_DEPARTMENT调用
--最后修改人:
--最后修改日期:
2016-04-19
--修改内容:
增加取展开的部门编码
DELETEDW_DIM_DEPARTMENT_TEMP;
INSERTINTODW_DIM_DEPARTMENT_TEMP
(PK_DEPTDOC,LONGDEPT,LONGDEPTCODE)
SELECTT.PK_DEPTDOC,GET_LONGDEPT(PK_DEPTDOC),GET_LONGDEPT_CODE(PK_DEPTDOC)
FROMDW_DIM_DEPARTMENTT;
UPDATEDW_DIM_DEPARTMENTT
SETLONGDEPT=
(SELECTL.LONGDEPTFROMDW_DIM_DEPARTMENT_TEMPLWHEREL.PK_DEPTDOC=T.PK_DEPTDOC)||'
>
'
LONGDEPTCODE=
(SELECTL.LONGDEPTCODEFROMDW_DIM_DEPARTMENT_TEMPLWHEREL.PK_DEPTDOC=T.PK_DEPTDOC)||'
;
COMMIT;
ENDSP_DW_DIM_DEPT_LONGDEPT;
2.1.2表结构
2.1.2.1DW_DIM_DEPARTMENT_TEMP
DW_DIM_DEPARTMENT_TEMP
createtableDW_DIM_DEPARTMENT_TEMP
(
PK_DEPTDOCCHAR(20)notnull,
LONGDEPTVARCHAR2(500),
LONGDEPTCODEVARCHAR2(500)
);
--Addcommentstothetable
commentontableDW_DIM_DEPARTMENT_TEMP
is'
部门信息'
--Addcommentstothecolumns
commentoncolumnDW_DIM_DEPARTMENT_TEMP.PK_DEPTDOC
部门档案主键'
commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPT
部门名称合并'
commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPTCODE
部门代码合并'
2.1.2.2DW_DIM_DEPARTMENT
createtableDW_DIM_DEPARTMENT
DEPTCODEVARCHAR2(40),
DEPTLEVELCHAR(20),
DEPTNAMEVARCHAR2(200),
DEPTSHORTNAMEVARCHAR2(200),
DEPTTYPENUMBER,
PK_FATHEDEPTCHAR(20),
PK_PSNDOCCHAR(20),
commentontableDW_DIM_DEPARTMENT
部门信息表'
commentoncolumnDW_DIM_DEPARTMENT.PK_DEPTDOC
commentoncolumnDW_DIM_DEPARTMENT.DEPTCODE
部门编码'
commentoncolumnDW_DIM_DEPARTMENT.DEPTLEVEL
部门级别'
commentoncolumnDW_DIM_DEPARTMENT.DEPTNAME
部门名称'
commentoncolumnDW_DIM_DEPARTMENT.DEPTSHORTNAME
部门简称'
commentoncolumnDW_DIM_DEPARTMENT.DEPTTYPE
部门类型'
commentoncolumnDW_DIM_DEPARTMENT.PK_FATHEDEPT
上级部门'
commentoncolumnDW_DIM_DEPARTMENT.PK_PSNDOC
负责人'
commentoncolumnDW_DIM_DEPARTMENT.LONGDEPT
commentoncolumnDW_DIM_DEPARTMENT.LONGDEPTCODE
--Create/Recreateprimary,uniqueandforeignkeyconstraints
altertableDW_DIM_DEPARTMENT
addconstraintPK_DW_DIM_DEPARTMENTprimarykey(PK_DEPTDOC);
2.1.2.3BD_DEPTDOC
CREATETABLEBD_DEPTDOC(
PK_DEPTDOCCHAR(20)NOTNULL,
DEPTCODEVARCHAR(40),
DEPTLEVELCHAR(20),
DEPTNAMEVARCHAR(200),
DEPTSHORTNAMEVARCHAR(200),
DEPTTYPEINTEGER,
PK_FATHEDEPTCHAR(20),
PK_PSNDOCCHAR(20)
COMMENTONTABLEBD_DEPTDOCIS'
部门档案'
COMMENTONCOLUMNBD_DEPTDOC.PK_DEPTDOCIS'
COMMENTONCOLUMNBD_DEPTDOC.DEPTCODEIS'
COMMENTONCOLUMNBD_DEPTDOC.DEPTLEVELIS'
COMMENTONCOLUMNBD_DEPTDOC.DEPTNAMEIS'
COMMENTONCOLUMNBD_DEPTDOC.DEPTSHORTNAMEIS'
COMMENTONCOLUMNBD_DEPTDOC.DEPTTYPEIS'
COMMENTONCOLUMNBD_DEPTDOC.PK_FATHEDEPTIS'
COMMENTONCOLUMNBD_DEPTDOC.PK_PSNDOCIS'
2.1.3表数据
2.1.3.1BD_DEPTDOC
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('
1001M01000000000002A'
'
0101'
0001M0100000000000X4'
总经办'
1001M01000000000002B'
010101'
维修工程部'
1001M01000000000002C'
01010101'
0001M0100000000000X5'
生产部'
1001M01000000000002D'
0101010101'
0001M0100000000000X6'
生产部一车间'
1001M01000000000002E'
010101010101'
0001M0100000000000X7'
生产部一车间机电组'
1001M01000000000002F'
010101010102'
生产部一车间电子组'
1001M01000000000002G'
0101010102'
生产部二车间'
1001M01000000000002H'
010101010201'
生产部二车间机电组'
1001M01000000000002I'
010101010202'
生产部二车间电子组'
1001M01000000000002J'
0101010103'
生产部三车间'
1001M01000000000002K'
010101010301'
生产部三车间EMB机队维护'
1001M01000000000002L'
010101010302'
生产部三车间外航维护'
1001M01000000000002M'
010101010303'
生产部三车间客舱维护组'
1001101000000005BNXQ'
010101010304'
生产部三车间机电(外航维护)组'
1001101000000005BNY3'
010101010305'
生产部三车间机电(EMB机队维护)组'
1001101000000005BNYL'
010101010306'
生产部三车间电子(EMB机队维护)组'
1001101000000005BNYP'
010101010307'
生产部三车间电子(客舱维护)组'
1001M01000000000002N'
0101010104'
生产部四车间'
1001M01000000000002O'
010101010401'
生产部四车间FedEx生产组'
1001M01000000000002P'
010101010402'
生产部四车间生产控制'
2.1.3.2DW_DIM_DEPARTMENT
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
INSERTINTODW_DIM_DEPAR