InformaticaPowerCenter调用存储过程

1/14InformaticaPowerCenter调用存储过程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：ZHS16GBK1.1.2服务器安装配置版本：Informatica9.6.1安装路径D:\Informatica\9.6.1\services域名：Domain_GZ-ETL01控制台访问地址：数据库(10.240.3.2)1.2.1软硬件配置服务器操作系统硬件配置资料库对应数据库OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bit资料库对应数据库字符集NLS_CHARACTERSET：ZHS16GBK工具Pl/sql8.0.2.15052/142实现过程本次示例使用组织部门层级无级树展开为横向层级关系，使用非连结存储过程调用。步骤：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_LONGDEPTISBEGIN--************************************************************************************************************--名称：SP_DW_DIM_DEPT_LONGDEPT--功能：将部门表组织层次无级转换为一行显示,部门名称、部门编码--作者：soutton--创建日期：2015-12-31--调度描述：暂无调度，提供给INFOMATICA的3/14mapping: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_DEPARTMENTTSETLONGDEPT=(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_TEMPDW_DIM_DEPARTMENT_TEMPcreatetableDW_DIM_DEPARTMENT_TEMP(PK_DEPTDOCCHAR(20)notnull,LONGDEPTVARCHAR2(500),LONGDEPTCODEVARCHAR2(500));--AddcommentstothetablecommentontableDW_DIM_DEPARTMENT_TEMPis'部门信息';--AddcommentstothecolumnscommentoncolumnDW_DIM_DEPARTMENT_TEMP.PK_DEPTDOC4/14is'部门档案主键';commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPTis'部门名称合并';commentoncolumnDW_DIM_DEPARTMENT_TEMP.LONGDEPTCODEis'部门代码合并';2.1.2.2DW_DIM_DEPARTMENTcreatetableDW_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));--AddcommentstothetablecommentontableDW_DIM_DEPARTMENTis'部门信息表';--AddcommentstothecolumnscommentoncolumnDW_DIM_DEPARTMENT.PK_DEPTDOCis'部门档案主键';commentoncolumnDW_DIM_DEPARTMENT.DEPTCODEis'部门编码';commentoncolumnDW_DIM_DEPARTMENT.DEPTLEVELis'部门级别';commentoncolumnDW_DIM_DEPARTMENT.DEPTNAMEis'部门名称';commentoncolumnDW_DIM_DEPARTMENT.DEPTSHORTNAMEis'部门简称';commentoncolumnDW_DIM_DEPARTMENT.DEPTTYPEis'部门类型';commentoncolumnDW_DIM_DEPARTMENT.PK_FATHEDEPTis'上级部门';commentoncolumnDW_DIM_DEPARTMENT.PK_PSNDOCis'负责人';commentoncolumnDW_DIM_DEPARTMENT.LONGDEPTis'部门名称合并';5/14commentoncolumnDW_DIM_DEPARTMENT.LONGDEPTCODEis'部门代码合并';--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableDW_DIM_DEPARTMENTaddconstraintPK_DW_DIM_DEPARTMENTprimarykey(PK_DEPTDOC);2.1.2.3BD_DEPTDOCCREATETABLEBD_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_DEPTDOCINSERTINTOBD_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','生产部6/14','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','生产部一车间电子组','1001M010000000000