Informatica PowerCenter調(diào)用存儲(chǔ)過(guò)程

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

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

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

4、并調(diào)試完成所需使用的存儲(chǔ)過(guò)程，。存儲(chǔ)過(guò)程：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 -功能： 將部門表組織層次無(wú)級(jí)轉(zhuǎn)換為一行顯示,部門名稱、部門編碼 -作者： soutton -創(chuàng)建日期： 2015-12-31 -調(diào)度描述： 暫無(wú)調(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 部門級(jí)別;comment on column DW_DIM_DEPARTMENT.DEPTNAME is 部門名稱;comment on column DW_DIM_DEPARTMENT.DEPTSHORTNAME is 部門簡(jiǎn)稱;comment on column DW_DIM_DEPARTMENT.DEPTTYPE is 部門類型;comment on column DW_DIM_DEPARTMENT.PK_FATHEDEPT is 上級(jí)部門;comment on column

11、DW_DIM_DEPARTMENT.PK_PSNDOC is 負(fù)責(zé)人;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 部門級(jí)別;COMMENT ON COLUMN BD_DEPTDOC.DEPTNAME IS 部門名稱;COMMENT ON COLUMN BD_DEPTDOC.DEPTSHORTNAME IS 部門簡(jiǎn)稱;COMMENT ON COLUMN BD_DEPTDOC.DEPTTYPE IS 部門類型;COMMENT ON COLUMN BD_DEPTDOC.PK_FATHEDEPT IS 上級(jí)部門;COMMENT

14、ON COLUMN BD_DEPTDOC.PK_PSNDOC IS 負(fù)責(zé)人;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)部一車間機(jī)電組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)部二車間機(jī)電組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機(jī)隊(duì)維護(hù)INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間外航維護(hù)INSERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三

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

19、SERT INTO BD_DEPTDOC( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（客艙維護(hù)）組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)部一車間機(jī)電組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)部二車間機(jī)電組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機(jī)隊(duì)維護(hù)INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME,

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

25、FATHEDEPT)生產(chǎn)部三車間機(jī)電（EMB機(jī)隊(duì)維護(hù)）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（EMB機(jī)隊(duì)維護(hù)）組INSERT INTO DW_DIM_DEPARTMENT( PK_DEPTDOC, DEPTCODE, DEPTLEVEL, DEPTNAME, PK_FATHEDEPT)生產(chǎn)部三車間電子（客艙維護(hù)）組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 -功能： 將部門表組織層次無(wú)級(jí)轉(zhuǎn)換為一行顯示 -作者： -創(chuàng)建日期： 2015-12-31 -調(diào)度描述： 暫無(wú)調(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