Informatica PowerCenter调用存储过程教学文案.docx
- 文档编号:8945785
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:19
- 大小:299.81KB
Informatica PowerCenter调用存储过程教学文案.docx
《Informatica PowerCenter调用存储过程教学文案.docx》由会员分享,可在线阅读,更多相关《Informatica PowerCenter调用存储过程教学文案.docx(19页珍藏版)》请在冰豆网上搜索。
InformaticaPowerCenter调用存储过程教学文案
InformaticaPowerCenter调用存储过程
InformaticaPowerCenter调用存储过程
1平台说明
1.1InformaticaETL(10.240.3.35)
1.1.1软硬件配置
Informatica版本
InformaticaPowerCenter9.6.164位
服务器操作系统
WinServer2008
硬件配置
内存:
16G、CPU:
处理器Intel(R)Xeon(R)CPUE7-2830@2.13GHz,1994Mhz,2个内核,2个逻辑处理器
资料库对应数据库
OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bit
资料库对应数据库字符集
NLS_CHARACTERSET:
ZHS16GBK
1.1.2服务器安装配置
版本:
Informatica9.6.1
安装路径
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
资料库对应数据库字符集
NLS_CHARACTERSET:
ZHS16GBK
工具
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调用
--最后修改人:
soutton
--最后修改日期:
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
is'部门档案主键';
commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPT
is'部门名称合并';
commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPTCODE
is'部门代码合并';
2.1.2.2DW_DIM_DEPARTMENT
createtableDW_DIM_DEPARTMENT
(
PK_DEPTDOCCHAR(20)notnull,
DEPTCODEVARCHAR2(40),
DEPTLEVELCHAR(20),
DEPTNAMEVARCHAR2(200),
DEPTSHORTNAMEVARCHAR2(200),
DEPTTYPENUMBER,
PK_FATHEDEPTCHAR(20),
PK_PSNDOCCHAR(20),
LONGDEPTVARCHAR2(500),
LONGDEPTCODEVARCHAR2(500)
);
--Addcommentstothetable
commentontableDW_DIM_DEPARTMENT
is'部门信息表';
--Addcommentstothecolumns
commentoncolumnDW_DIM_DEPARTMENT.PK_DEPTDOC
is'部门档案主键';
commentoncolumnDW_DIM_DEPARTMENT.DEPTCODE
is'部门编码';
commentoncolumnDW_DIM_DEPARTMENT.DEPTLEVEL
is'部门级别';
commentoncolumnDW_DIM_DEPARTMENT.DEPTNAME
is'部门名称';
commentoncolumnDW_DIM_DEPARTMENT.DEPTSHORTNAME
is'部门简称';
commentoncolumnDW_DIM_DEPARTMENT.DEPTTYPE
is'部门类型';
commentoncolumnDW_DIM_DEPARTMENT.PK_FATHEDEPT
is'上级部门';
commentoncolumnDW_DIM_DEPARTMENT.PK_PSNDOC
is'负责人';
commentoncolumnDW_DIM_DEPARTMENT.LONGDEPT
is'部门名称合并';
commentoncolumnDW_DIM_DEPARTMENT.LONGDEPTCODE
is'部门代码合并';
--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','总经办','');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002B','010101','0001M0100000000000X4','维修工程部','1001M01000000000002A');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002C','01010101','0001M0100000000000X5','生产部','1001M01000000000002B');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002D','0101010101','0001M0100000000000X6','生产部一车间','1001M01000000000002C');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002E','010101010101','0001M0100000000000X7','生产部一车间机电组','1001M01000000000002D');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002F','010101010102','0001M0100000000000X7','生产部一车间电子组','1001M01000000000002D');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002G','0101010102','0001M0100000000000X6','生产部二车间','1001M01000000000002C');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002H','010101010201','0001M0100000000000X7','生产部二车间机电组','1001M01000000000002G');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002I','010101010202','0001M0100000000000X7','生产部二车间电子组','1001M01000000000002G');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002J','0101010103','0001M0100000000000X6','生产部三车间','1001M01000000000002C');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002K','010101010301','0001M0100000000000X7','生产部三车间EMB机队维护','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002L','010101010302','0001M0100000000000X7','生产部三车间外航维护','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002M','010101010303','0001M0100000000000X7','生产部三车间客舱维护组','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001101000000005BNXQ','010101010304','0001M0100000000000X7','生产部三车间机电(外航维护)组','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001101000000005BNY3','010101010305','0001M0100000000000X7','生产部三车间机电(EMB机队维护)组','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001101000000005BNYL','010101010306','0001M0100000000000X7','生产部三车间电子(EMB机队维护)组','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001101000000005BNYP','010101010307','0001M0100000000000X7','生产部三车间电子(客舱维护)组','1001M01000000000002J');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002N','0101010104','0001M0100000000000X6','生产部四车间','1001M01000000000002C');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002O','010101010401','0001M0100000000000X7','生产部四车间FedEx生产组','1001M01000000000002N');
INSERTINTOBD_DEPTDOC(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002P','010101010402','0001M0100000000000X7','生产部四车间生产控制','1001M01000000000002N');
2.1.3.2DW_DIM_DEPARTMENT
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002A','0101','0001M0100000000000X4','总经办','');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002B','010101','0001M0100000000000X4','维修工程部','1001M01000000000002A');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002C','01010101','0001M0100000000000X5','生产部','1001M01000000000002B');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002D','0101010101','0001M0100000000000X6','生产部一车间','1001M01000000000002C');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002E','010101010101','0001M0100000000000X7','生产部一车间机电组','1001M01000000000002D');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002F','010101010102','0001M0100000000000X7','生产部一车间电子组','1001M01000000000002D');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002G','0101010102','0001M0100000000000X6','生产部二车间','1001M01000000000002C');
INSERTINTODW_DIM_DEPARTMENT(PK_DEPTDOC,DEPTCODE,DEPTLEVEL,DEPTNAME,PK_FATHEDEPT)
VALUES('1001M01000000000002H','010101010201','0001M0100000000000X7','生产部二车间机电组','1001M01000000000002G');
INSERTINTODW_DIM_DEPAR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Informatica PowerCenter调用存储过程教学文案 PowerCenter 调用 存储 过程 教学 文案