Informatica PowerCenter调用存储过程教学文案Word下载.docx

1、安装路径D:Informatica9.6.1services域名：Domain_GZ-ETL01控制台访问地址：http:/10.33.2.235:6008/administrator/1.2 BI数据库（10.240.3.2）1.2.1 软硬件配置Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit工具Pl/sql 8.0.2.15052 实现过程本次示例使用组织部门层级无级树展开为横向层级关系，使用非连结存储过程调用。步骤：1） 存储过程由组织部门层级无级树DW_DIM_DEPARTMENT表读出，调用定义递归函

2、数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.1 BI数据库创建存储过程在目标数据库中创建并调试完成所需使用的存储过程，。存储过程：SP_DW_DIM_DEPT_LONGDEPT为例，结果如下：2.1.1 SP_DW_DIM_DEPT_

3、LONGDEPT:CREATE OR REPLACE PROCEDURE SP_DW_DIM_DEPT_LONGDEPT ISBEGIN -* -名称： SP_DW_DIM_DEPT_LONGDEPT -功能： 将部门表组织层次无级转换为一行显示,部门名称、部门编码 -作者： soutton -创建日期： 2015-12-31 -调度描述： 暂无调度， 提供给INFOMATICA的mapping:m_DW_DIM_DEPARTMENT调用 -最后修改人: -最后修改日期: 2016-04-19 -修改内容: 增加取展开的部门编码 DELETE DW_DIM_DEPARTMENT_TEMP; I

4、NSERT INTO DW_DIM_DEPARTMENT_TEMP （PK_DEPTDOC, LONGDEPT, LONGDEPTCODE） SELECT T.PK_DEPTDOC, GET_LONGDEPT（PK_DEPTDOC）, GET_LONGDEPT_CODE（PK_DEPTDOC） FROM DW_DIM_DEPARTMENT T; UPDATE DW_DIM_DEPARTMENT T SET LONGDEPT = （SELECT L.LONGDEPT FROM DW_DIM_DEPARTMENT_TEMP L WHERE L.PK_DEPTDOC = T.PK_DEPTDOC）

5、| , LONGDEPTCODE = （SELECT L.LONGDEPTCODE FROM DW_DIM_DEPARTMENT_TEMP L WHERE L.PK_DEPTDOC = T.PK_DEPTDOC） | ; COMMIT;END SP_DW_DIM_DEPT_LONGDEPT;2.1.2 表结构2.1.2.1 DW_DIM_DEPARTMENT_TEMPDW_DIM_DEPARTMENT_TEMPcreate table DW_DIM_DEPARTMENT_TEMP（ PK_DEPTDOC CHAR（20） not null, LONGDEPT VARCHAR2（500）, LO

6、NGDEPTCODE VARCHAR2（500）;- Add comments to the table comment on table DW_DIM_DEPARTMENT_TEMP is 部门信息- Add comments to the columns comment on column DW_DIM_DEPARTMENT_TEMP.PK_DEPTDOC部门档案主键comment on column DW_DIM_DEPARTMENT_TEMP.LONGDEPT部门名称合并comment on column DW_DIM_DEPARTMENT_TEMP.LONGDEPTCODE部门代码合

7、并2.1.2.2 DW_DIM_DEPARTMENTcreate table DW_DIM_DEPARTMENT DEPTCODE VARCHAR2（40）, DEPTLEVEL CHAR（20）, DEPTNAME VARCHAR2（200）, DEPTSHORTNAME VARCHAR2（200）, DEPTTYPE NUMBER, PK_FATHEDEPT CHAR（20）, PK_PSNDOC CHAR（20）,comment on table DW_DIM_DEPARTMENT部门信息表comment on column DW_DIM_DEPARTMENT.PK_DEPTDOCcom

8、ment on column DW_DIM_DEPARTMENT.DEPTCODE部门编码comment on column DW_DIM_DEPARTMENT.DEPTLEVEL部门级别comment on column DW_DIM_DEPARTMENT.DEPTNAME部门名称comment on column DW_DIM_DEPARTMENT.DEPTSHORTNAME部门简称comment on column DW_DIM_DEPARTMENT.DEPTTYPE部门类型comment on column DW_DIM_DEPARTMENT.PK_FATHEDEPT上级部门comme

9、nt on column DW_DIM_DEPARTMENT.PK_PSNDOC负责人comment on column DW_DIM_DEPARTMENT.LONGDEPTcomment on column DW_DIM_DEPARTMENT.LONGDEPTCODE- Create/Recreate primary, unique and foreign key constraints alter table DW_DIM_DEPARTMENT add constraint PK_DW_DIM_DEPARTMENT primary key （PK_DEPTDOC）;2.1.2.3 BD_D

10、EPTDOCCREATE TABLE BD_DEPTDOC（PK_DEPTDOC CHAR（20） NOT NULL,DEPTCODE VARCHAR（40）,DEPTLEVEL CHAR（20）,DEPTNAME VARCHAR（200）,DEPTSHORTNAME VARCHAR（200）,DEPTTYPE INTEGER,PK_FATHEDEPT CHAR（20）,PK_PSNDOC CHAR（20）COMMENT ON TABLE BD_DEPTDOC IS 部门档案COMMENT ON COLUMN BD_DEPTDOC.PK_DEPTDOC IS COMMENT ON COLUMN

11、 BD_DEPTDOC.DEPTCODE IS COMMENT ON COLUMN BD_DEPTDOC.DEPTLEVEL IS COMMENT ON COLUMN BD_DEPTDOC.DEPTNAME IS COMMENT ON COLUMN BD_DEPTDOC.DEPTSHORTNAME IS COMMENT ON COLUMN BD_DEPTDOC.DEPTTYPE IS COMMENT ON COLUMN BD_DEPTDOC.PK_FATHEDEPT IS COMMENT ON COLUMN BD_DEPTDOC.PK_PSNDOC IS 2.1.3 表数据2.1.3.1 BD

12、_DEPTDOCINSERT INTO BD_DEPTDOC（ PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT）VALUES （1001M01000000000002A,01010001M0100000000000X4总经办1001M01000000000002B010101维修工程部1001M01000000000002C010101010001M0100000000000X5生产部1001M01000000000002D01010101010001M0100000000000X6生产部一车间1001M0100000000000

13、2E0101010101010001M0100000000000X7生产部一车间机电组1001M01000000000002F010101010102生产部一车间电子组1001M01000000000002G0101010102生产部二车间1001M01000000000002H010101010201生产部二车间机电组1001M01000000000002I010101010202生产部二车间电子组1001M01000000000002J0101010103生产部三车间1001M01000000000002K010101010301生产部三车间EMB机队维护1001M010000000000

14、02L010101010302生产部三车间外航维护1001M01000000000002M010101010303生产部三车间客舱维护组1001101000000005BNXQ010101010304生产部三车间机电（外航维护）组1001101000000005BNY3010101010305生产部三车间机电（EMB机队维护）组1001101000000005BNYL010101010306生产部三车间电子（EMB机队维护）组1001101000000005BNYP010101010307生产部三车间电子（客舱维护）组1001M01000000000002N0101010104生产部四车间1001M01000000000002O010101010401生产部四车间FedEx生产组1001M01000000000002P010101010402生产部四车间生产控制2.1.3.2 DW_DIM_DEPARTMENTINSERT INTO DW_DIM_DEPARTMENT（ PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT）INSERT INTO DW_DIM_DEPAR