Informatica PowerCenter調(diào)用存儲過程

1、Informatica PowerCenter調(diào)用存儲過程1 平臺說明1.1 Informatica ETL(.35) 1.1.1 軟硬件配置Informatica版本Informatica PowerCenter 64位服務器操作系統(tǒng)Win Server2008硬件配置內(nèi)存:16G、CPU: 處理器Intel(R) Xeon(R) CPU E7- 2830 2.13GHz，1994 Mhz，2 個內(nèi)核，2 個邏輯處理器資料庫對應數(shù)據(jù)庫Oracle Database 11g Enterprise Edition Release - 64bit資料庫對應數(shù)據(jù)庫字符集NLS_CHARACTERSE

2、T：ZHS16GBK1.1.2 服務器安裝配置版本：Informatica 安裝路徑D:Informatica9.6.1services域名：Domain_GZ-ETL01控制臺訪問地址：35:6008/administrator/1.2 BI數(shù)據(jù)庫.2)1.2.1 軟硬件配置服務器操作系統(tǒng)硬件配置資料庫對應數(shù)據(jù)庫Oracle Database 11g Enterprise Edition Release .0 - 64bit資料庫對應數(shù)據(jù)庫字符集NLS_CHARACTERSET：ZHS16GBK工具Pl/sql2 實現(xiàn)過程本次示例使用組織部門層級

3、無級樹展開為橫向?qū)蛹夑P系，使用非連結(jié)存儲過程調(diào)用。步驟：1) 存儲過程由組織部門層級無級樹DW_DIM_DEPARTMENT表讀出，調(diào)用定義遞歸函數(shù)GET_LONGDEPT、GET_LONGDEPT_CODE寫入到橫向展開臨時表DW_DIM_DEPARTMENT_TEMP；2) 再將展開臨時表DW_DIM_DEPARTMENT_TEMP的層級更新到DW_DIM_DEPARTMENT表；3) 于PowerCenter中當DW_DIM_DEPARTMENT由源表BD_DEPTDOC全表更新后，再調(diào)用SP_DW_DIM_DEPT_LONGDEPT實現(xiàn)；2.1 BI數(shù)據(jù)庫創(chuàng)建存儲過程在目標數(shù)據(jù)庫中創(chuàng)建

4、并調(diào)試完成所需使用的存儲過程，。存儲過程：SP_DW_DIM_DEPT_LONGDEPT為例，結(jié)果如下：2.1.1 SP_DW_DIM_DEPT_LONGDEPT:CREATE OR REPLACE PROCEDURE SP_DW_DIM_DEPT_LONGDEPT ISBEGIN -* -名稱： SP_DW_DIM_DEPT_LONGDEPT -功能： 將部門表組織層次無級轉(zhuǎn)換為一行顯示,部門名稱、部門編碼 -作者： soutton -創(chuàng)建日期： 2015-12-31 -調(diào)度描述： 暫無調(diào)度， 提供給INFOMATICA的mapping:m_DW_DIM_DEPARTMENT調(diào)用 -最后修改

5、人: soutton -最后修改日期: 2016-04-19 -修改內(nèi)容: 增加取展開的部門編碼 -* 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 =

6、(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 表結(jié)構(gòu) DW_DIM_DEPARTMENT_TEMPDW_DIM_DEPARTMENT_TEMPcreate ta

7、ble 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_DI

8、M_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,

9、 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 i

10、s 部門編碼;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

11、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

12、); 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 部

13、門檔案主鍵;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

14、ON COLUMN BD_DEPTDOC.PK_PSNDOC IS 負責人;2.1.3 表數(shù)據(jù) BD_DEPTDOCINSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)總經(jīng)辦,);INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)維修工程部INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAM

15、E, PK_FATHEDEPT)生產(chǎn)部INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間機電組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間電子組INSERT INTO BD_DE

16、PTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間機電組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間電子組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEV

17、EL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間EMB機隊維護INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間外航維護INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三

18、車間客艙維護組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間機電（外航維護）組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間機電（EMB機隊維護）組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（EMB機隊維護）組IN

19、SERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（客艙維護）組INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間FedEx生產(chǎn)組INSERT INTO BD_DEPTDOC( PK_

20、DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間生產(chǎn)控制 DW_DIM_DEPARTMENTINSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)總經(jīng)辦,);INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)維修工程部INSERT INTO DW_DIM_DEPARTME

21、NT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間機電組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTC

22、ODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部一車間電子組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間機電組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL,

23、 DEPTNAME, PK_FATHEDEPT)生產(chǎn)部二車間電子組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間EMB機隊維護INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME,

24、PK_FATHEDEPT)生產(chǎn)部三車間外航維護INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間客艙維護組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間機電（外航維護）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_

25、FATHEDEPT)生產(chǎn)部三車間機電（EMB機隊維護）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（EMB機隊維護）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（客艙維護）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, D

26、EPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間FedEx生產(chǎn)組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部四車間生產(chǎn)控制2.1.4 自定義函數(shù) GET_LONGDEPTCREATE OR REPLACE FUNCTION GET_LONGDEPT(DEP

27、T_ID IN VARCHAR2) RETURN VARCHAR2 IS V_LONGDEPT VARCHAR2(500); V_DEPT_ID VARCHAR2(30);BEGIN -* -名稱： GET_LONGDEPT -功能： 將部門表組織層次無級轉(zhuǎn)換為一行顯示 -作者： -創(chuàng)建日期： 2015-12-31 -調(diào)度描述： 暫無調(diào)度， 提供給SP_DW_DIM_DEPT_LONGDEPT調(diào)用 -* SELECT DEPTNAME, PK_FATHEDEPT INTO V_LONGDEPT, V_DEPT_ID FROM DW_DIM_DEPARTMENT WHERE PK_DEPTDOC = DEPT_ID; IF V_DEPT_ID IS NULL THEN RETURN(V_LONGDEPT); ELSE V_LONGDEPT := GET_LONGDEPT(V_DEPT_ID) | | V_LONGDEPT; RETURN(V_LONGDEPT