Inatica PowerCenter调用存储过程.doc

Informatica PowerCenter调用存储过程1 平台说明1.1 Informatica ETL(5) 1.1.1 软硬件配置Informatica版本Informatica PowerCenter 9.6.1 64位服务器操作系统Win Server2008硬件配置内存:16G、CPU: 处理器Intel(R) Xeon(R) CPU E7- 2830 2.13GHz，1994 Mhz，2 个内核，2 个逻辑处理器资料库对应数据库Oracle Database 11g Enterprise Edition Release .0 - 64bit资料库对应数据库字符集NLS_CHARACTERSET：ZHS16GBK1.1.2 服务器安装配置版本：Informatica 9.6.1安装路径D:Informatica9.6.1services域名：Domain_GZ-ETL01控制台访问地址：35:6008/administrator/1.2 BI数据库()1.2.1 软硬件配置服务器操作系统硬件配置资料库对应数据库Oracle Database 11g Enterprise Edition Release .0 - 64bit资料库对应数据库字符集NLS_CHARACTERSET：ZHS16GBK工具Pl/sql 5052 实现过程本次示例使用组织部门层级无级树展开为横向层级关系，使用非连结存储过程调用。步骤：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.1 BI数据库创建存储过程在目标数据库中创建并调试完成所需使用的存储过程，。存储过程：SP_DW_DIM_DEPT_LONGDEPT为例，结果如下：2.1.1 SP_DW_DIM_DEPT_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调用 -最后修改人: soutton -最后修改日期: 2016-04-19 -修改内容: 增加取展开的部门编码 -* DELETE DW_DIM_DEPARTMENT_TEMP; INSERT 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) | , 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 表结构 DW_DIM_DEPARTMENT_TEMPDW_DIM_DEPARTMENT_TEMPcreate table DW_DIM_DEPARTMENT_TEMP( PK_DEPTDOC CHAR(20) not null, LONGDEPT VARCHAR2(500), LONGDEPTCODE 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 is 部门档案主键;comment on column DW_DIM_DEPARTMENT_TEMP.LONGDEPT is 部门名称合并;comment on column DW_DIM_DEPARTMENT_TEMP.LONGDEPTCODE is 部门代码合并; DW_DIM_DEPARTMENTcreate table DW_DIM_DEPARTMENT( PK_DEPTDOC CHAR(20) not null, DEPTCODE VARCHAR2(40), DEPTLEVEL CHAR(20), DEPTNAME VARCHAR2(200), DEPTSHORTNAME VARCHAR2(200), DEPTTYPE NUMBER, PK_FATHEDEPT CHAR(20), PK_PSNDOC CHAR(20), LONGDEPT VARCHAR2(500), LONGDEPTCODE VARCHAR2(500);- Add comments to the table comment on table DW_DIM_DEPARTMENT is 部门信息表;- Add comments to the columns comment on column DW_DIM_DEPARTMENT.PK_DEPTDOC is 部门档案主键;comment on column DW_DIM_DEPARTMENT.DEPTCODE is 部门编码;comment on column DW_DIM_DEPARTMENT.DEPTLEVEL is 部门级别;comment on column DW_DIM_DEPARTMENT.DEPTNAME is 部门名称;comment on column DW_DIM_DEPARTMENT.DEPTSHORTNAME is 部门简称;comment on column DW_DIM_DEPARTMENT.DEPTTYPE is 部门类型;comment on column DW_DIM_DEPARTMENT.PK_FATHEDEPT is 上级部门;comment on column DW_DIM_DEPARTMENT.PK_PSNDOC is 负责人;comment on column DW_DIM_DEPARTMENT.LONGDEPT is 部门名称合并;comment on column DW_DIM_DEPARTMENT.LONGDEPTCODE is 部门代码合并;- Create/Recreate primary, unique and foreign key constraints alter table DW_DIM_DEPARTMENT add constraint PK_DW_DIM_DEPARTMENT primary key (PK_DEPTDOC); BD_DEPTDOCCREATE 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 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 表数据 BD_DEPTDOCINSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002A,0101,0001M0100000000000X4,总经办,);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002B,010101,0001M0100000000000X4,维修工程部,1001M01000000000002A);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002C,01010101,0001M0100000000000X5,生产部,1001M01000000000002B);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002D,0101010101,0001M0100000000000X6,生产部一车间,1001M01000000000002C);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002E,010101010101,0001M0100000000000X7,生产部一车间机电组,1001M01000000000002D);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002F,010101010102,0001M0100000000000X7,生产部一车间电子组,1001M01000000000002D);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002G,0101010102,0001M0100000000000X6,生产部二车间,1001M01000000000002C);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002H,010101010201,0001M0100000000000X7,生产部二车间机电组,1001M01000000000002G);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002I,010101010202,0001M0100000000000X7,生产部二车间电子组,1001M01000000000002G);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002J,0101010103,0001M0100000000000X6,生产部三车间,1001M01000000000002C);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002K,010101010301,0001M0100000000000X7,生产部三车间EMB机队维护,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002L,010101010302,0001M0100000000000X7,生产部三车间外航维护,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002M,010101010303,0001M0100000000000X7,生产部三车间客舱维护组,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNXQ,010101010304,0001M0100000000000X7,生产部三车间机电（外航维护）组,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNY3,010101010305,0001M0100000000000X7,生产部三车间机电（EMB机队维护）组,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNYL,010101010306,0001M0100000000000X7,生产部三车间电子（EMB机队维护）组,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNYP,010101010307,0001M0100000000000X7,生产部三车间电子（客舱维护）组,1001M01000000000002J);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002N,0101010104,0001M0100000000000X6,生产部四车间,1001M01000000000002C);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002O,010101010401,0001M0100000000000X7,生产部四车间FedEx生产组,1001M01000000000002N);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002P,010101010402,0001M0100000000000X7,生产部四车间生产控制,1001M01000000000002N); DW_DIM_DEPARTMENTINSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002A,0101,0001M0100000000000X4,总经办,);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002B,010101,0001M0100000000000X4,维修工程部,1001M01000000000002A);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002C,01010101,0001M0100000000000X5,生产部,1001M01000000000002B);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002D,0101010101,0001M0100000000000X6,生产部一车间,1001M01000000000002C);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002E,010101010101,0001M0100000000000X7,生产部一车间机电组,1001M01000000000002D);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002F,010101010102,0001M0100000000000X7,生产部一车间电子组,1001M01000000000002D);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002G,0101010102,0001M0100000000000X6,生产部二车间,1001M01000000000002C);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002H,010101010201,0001M0100000000000X7,生产部二车间机电组,1001M01000000000002G);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002I,010101010202,0001M0100000000000X7,生产部二车间电子组,1001M01000000000002G);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002J,0101010103,0001M0100000000000X6,生产部三车间,1001M01000000000002C);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002K,010101010301,0001M0100000000000X7,生产部三车间EMB机队维护,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002L,010101010302,0001M0100000000000X7,生产部三车间外航维护,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002M,010101010303,0001M0100000000000X7,生产部三车间客舱维护组,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNXQ,010101010304,0001M0100000000000X7,生产部三车间机电（外航维护）组,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNY3,010101010305,0001M0100000000000X7,生产部三车间机电（EMB机队维护）组,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNYL,010101010306,0001M0100000000000X7,生产部三车间电子（EMB机队维护）组,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001101000000005BNYP,010101010307,0001M0100000000000X7,生产部三车间电子（客舱维护）组,1001M01000000000002J);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002N,0101010104,0001M0100000000000X6,生产部四车间,1001M01000000000002C);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002O,010101010401,0001M0100000000000X7,生产部四车间FedEx生产组,1001M01000000000002N);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)VALUES (1001M01000000000002P,010101010402,0001M0100000000000X7,生产部四车间生产控制,1001M01000000000002N);2.1.4 自定义函数 GET_LONGDEPTCREATE OR REPLACE FUNCTION GET_LONGDEPT(DEPT_ID IN VARCHAR2) RETURN VARCHAR2 IS V_LONGDEPT VARCHAR2(500); V_DEPT_ID VARCHAR2(30);BEGIN -* -名称： GE