infa

一、Informatica介紹

Informatica PowerCenter 是Informatica公司開發的世界級的企業資料內建平台，也是業界領先的ETL工具。

Informatica PowerCenter使使用者能夠友善地從異構的已有系統和資料源中抽取資料，

用來建立、部署、管理企業的資料倉庫，進而幫助企業做出快速、正确的決策。

此産品為滿足企業級要求而設計，可以提供企業部門的資料和電子商務資料源之間的內建，

如XML，網站日志，關系型資料，主機和遺留系統等資料源。

此平台性能可以滿足企業分析最嚴格的要求。 

Informatica PowerCenter已被全球多家著名企業用來建設BI/DW系統，

它可內建和分析企業的關鍵商務資訊，優化整個商務價值鍊的表現和響應速度。

Informatica PowerCenter擁有一個功能強大的資料整合引擎，

所有的資料抽取、轉換、裝載的功能都是在記憶體中執行的，不需要開發者手工編寫這些過程的代碼。

Informatica PowerCenter 資料整合引擎是中繼資料驅動的，

通過知識庫和引擎的配對管理，可以保證資料整合過程能夠最優化執行，

并且使資料倉庫管理者比較容易對系統進行分析管理，進而适應日益增加的資料裝載和使用者群。

二、informatica開發步驟：

定義源： Desinger

定義目标： Desinger

建立映射: 源--》轉換元件--》目标

Mapping都是由源表、轉換元件和目标表組成，用于描述資料抽取的過程。

源表和目标表可以從源和目标檔案夾拖拽到工作區，轉換元件需要手動建立。

Mapping設計的關鍵就是轉換元件的使用，這關系到資料抽取的正确性和效率。

定義任務: Workflow Manager

建立工作流: Workflow Manager

工作流排程監控 : Workflow Monitor

三、轉換元件

1. 元件清單

Source Qualifier 從資料源讀取資料

Expression 行級轉換

Filter 資料過濾

Sorter 資料排序

Router 條件分發

joiner 異構資料關聯連接配接

Lookup 查詢連接配接

Aggregator 聚合

Update Strategy 對目标編輯(insert\update\delete\reject)

Union 資料合并

Sequence Generator 序列号生成器

Normalizer 記錄規範化

Rank 對記錄進行TOPx

Transaction Control 對裝載資料按條件進行事物控制

Stored Procedure 存儲過程元件

Custom 使用者自定義元件

HTTP WWW元件

Java Java自程式設計元件

其它應用元件

2. 元件類型

Passive類型：流入流出元件的行數不發生變化，如：Expression、Lookup、HTTP元件。

Active類型 ：流入流出元件的行數會發生變化，如：Aggregator、Router、Filter元件。

Connected ：表示該轉換元件需要連接配接到資料流。

Unconnected ：表示該轉換元件可以不連接配接到資料流。

四、元件

1.Source Qualifier元件

作用：根據查詢SQL從關系表讀取資料或直接從檔案中讀取資料。

用法：建立好Source Qualifier後，将需要用的字段從源表拖拽到SQ，輕按兩下SQ，對SQ屬性進行設定。

命名：SQ_Name

類型：Active/Connected

屬性解釋：

Sql Query: 使用者定義的 SQL 語句。先生成SQL，再進行改寫。

User Dfined Jion:聯接不同源的 WHERE 子句。連接配接多張表的關聯條件。

Source Filter:對源記錄應用的篩選器。非連接配接條件外的其他查詢條件。

Number Of Sorted Ports:用于排序的輸入端口數量

Select Distinct：僅選擇相異值。去重。

Pre SQL：在管道運作之前使用源資料庫連接配接執行的 SQL 語句。

Post SQL：在管道運作之後使用源資料庫連接配接執行的 SQL 語句。

Output is deterministic：在會話運作期間源資料不更改。

Output is repeatable：源資料已排序。

注意：

N個同構資料源隻需要1個Source Qualify元件關聯，

關聯時建議将Source設計區将需要關聯的源表手工用連線關聯起來，

在Mapping設計區删除各個源所帶的Source Qualify元件，

用一個新的Source Qualify元件包含所有源的字段内容。

例2：從emp表中隻複制20号部門的資料到t_emp_2中

Source Qualifier元件--》“屬性”頁簽--》Source Filter=“EMP.deptno=20”

例3：從emp和dept兩張源表複制30号部門的資料到目标表t_emp_3

拖入兩張源表，删除單一的Source Qualifier元件，點SQ重新生成Source Qualifier元件。

User Defined Join=“DEPT.DEPTNO=EMP.DEPTNO”

Source Filter=“EMP.deptno=30”

作業1：

源表：學生表、課程表、成績表(建表語句和資料見《學生表及資料.txt》) 

目标表：(學生名、出生日期、課程名、講師、成績)

create table t_students(

s_name varchar2(20),

birth date,

c_name varchar2(20),

t_name varchar2(20),

grade number(3)

);

從3個源表向目标表t_students抽取資料。

注意：如果有大資料量的表，則可能導緻記憶體被沾滿，其他表加載不上。

2.Expression 元件

作用：

基于行級的資料項指派、修改、計算；

在同行記錄中可新增、減少資料項。

用法：

建立好Expression 元件後，将需要用到的字段從其他元件拖拽到EXP中，輕按兩下EXP，新增輸出端口，編輯該端口的表達式。

命名：EXP_Name

類型：Passive/Connected

應用範圍：

資料類型轉換,例如Expression:to_date(Port1,'yyyymmdd')

資料項計算，例如Expression:(Port1,Port2)/Port3

新增變量，例如Expression:i=i+1

I項： 流入元件的資料項

O項：流出元件的資料項

V項：元件中定義的變量

Expression項： 對資料進行計算的表達式 

Default value項：當資料項為空時的預設值

例4：從emp表中複制資料到t_emp_4中，

t_emp_4表記錄年薪并對職位進行+部門号處理，

作業：

源表：EMP 

目标表：emp_zuoye_2

功能：工資加500，職員姓名改小寫

3.Filter 元件

作用：

對流入元件中的記錄資料進行過濾；

類似于關系型資料庫的where;

與Source Qualifier的過濾功能差別在執行位置上。

用法：建立好Filter 元件後，将需要的用的字段從其他元件中拖拽到FIL中，

輕按兩下FIL，對FIL的屬性進行設定，Filter Condition寫入類似where條件的表達式即可。

命名：FIL_Name

類型：Active/Connected

屬性解釋：

Filter Condition：對記錄應用的篩選器。where條件。

Tracing Level：會話日志中的詳細資訊量

注意：

在Source Qualifier中User Defined Jion、Source Filter、Number of Sorted Ports中定義了sql條件，

那麼，在Filter 元件中就不要再重寫相關的sql條件，否則，将不會生效。

例5：從檔案或emp表中複制10号部門的資料到t_emp_5中

構造源檔案emp.txt, 源--》從檔案導入

E:\Informatica\9.6.1\server\infa_shared\SrcFiles\emp.txt

作業2：

源表：成績檔案

目标表：(學生号、課程号、成績)

要求：隻輸出大于等于60的成績資訊

4.Router元件

作用：

對流入元件中的記錄資料按照條件進行分發。

用法：在組頁簽增加分組和分組條件。

命名：RT_Name

類型：Active/Connected

屬性解釋：

Tracing Level：會話日志中的詳細資訊量

應用範圍：資料分發

例6：将emp表的資料按照部門分别導入到T_emp6_10、T_emp6_20、T_emp6_30三張表内。

源：emp表

目标：T_emp6_10、T_emp6_20、T_emp6_30三張表

用位置關聯

ora-26002 将target load type改為normal

作業3：

源表：成績表

目标表：T_grade_80、T_grade_60、T_grade_default (學生号、課程号、成績)

要求：

T_grade_80儲存成績在80-100的成績表資訊；

T_grade_60儲存成績在60-79的成績表資訊；

T_grade_default儲存成績在0-59的成績表資訊。

5.Joiner元件 

作用：

對異構資料進行關聯（連接配接查詢來自不同資料庫的表或者檔案），同構資料關聯使用Source Qualify元件；

類似于SQL中的Join語句。

用法：建立好Joiner元件後，将需要用的字段從轉換元件拖拽到Joiner中，輕按兩下Joiner，對屬性進行設定。

用于連接配接的兩張表分為為Master和Detail，選取小表為Master，大表為Detail即可。

命名：JNR_Name

類型：Active/Connected

屬性解釋：

Cache Directory：用于緩存主記錄的目錄

Join Condition：聯接條件

Join Type：聯接類型分為4種，Normal Join等值連接配接，Master Outer Join左外連接配接，Detail Outer Join右外連接配接,Full Outer Join全連結

Null ordering in master：确定空值在主關系中是最高值還是最低值

Null ordering in detail：确定空值在詳細關系中是最高值還是最低值

Tracing Level：會話日志中的詳細資訊量

Joiner Data Cache Size：聯接器緩存中用于存儲資料的容量(以位元組為機關)

Joiner Index Cache Size：聯接器索引緩存中用于存儲資料的容量(以位元組為機關)

Sorted Input：定義是否對輸入資料進行排序。

Master Sort Order：定義主項的排序順序: 升序或降序。

Transformation Scope：定義對輸入資料執行轉換的範圍

應用範圍：2個異構資料類型的表關聯。

注意：

将資料量少的設定為Master；

需要對SQ中連接配接字段進行改名處理；

盡量多使用Normal Join 和 Master Outer Join兩種類型連接配接，因為其它兩種連接配接效率低。

5.1 同構、異構Joins對比

同種結構資料 不同DB或Flat與Flat之間

Source Qualify元件 Joiner元件

從源直接Joins 在PowerCenter中Joins

n個源可用1個SQ元件 n個SQ元件加上n-1個joins元件

在源中先定義連接配接關系 在Joiner元件中定義連接配接條件

5.2 Master和Detail選取

選取記錄量小的資料源作為Master（選中M）,

則另一個資料源為Detail，

Join條件為Master.主鍵列=Detail.主/外鍵列

5.3 Join類型

Normal Join 等值連接配接

Master Outer Join Detail全外連接配接

Detail Outer Join Master全外連接配接

Full Outer Join 全連接配接

例7：

描述：從emp.txt檔案和dept表中等值連接配接并複制資料到t_emp_7。

源表：emp.txt檔案、dept表

目标：t_emp_7

要點：用joiner元件進行Mapping設計

作業4：

源表：學生表、課程表、成績檔案

目标表：(學生名、出生日期、課程名、講師、成績)

步驟：

将源表學生表、課程表通過SQ先連接配接，再通過Joiner元件連接配接成績檔案，最後到目标表。

5.4 Sorter元件

作用：排序。

按照某個或多個字段進行排序(升序或降序)。

用法：建立好Sorter元件後，将需要用的字段從轉換元件拖拽到Sorter中，輕按兩下Sorter，對屬性進行設定。

在Ports頁簽標明key，再選擇asc/desc。

命名：Sort_Name

類型：Active/Connected

屬性解釋：

Case Sensitive:排序時大小寫是否敏感。

Distinct：擷取唯一值。實作了sql中的Distinct功能。

Null Treated Low:是否将null值認為是小數。

例7-2：

描述：對例7進行改造，加入排序元件，以優化執行速度。

源表：emp.txt檔案、dept表

目标：t_emp_7

要點：使用sorter元件對emp.txt檔案、dept表中的deptno列進行排序，勾選Joiner元件的Sorted Input屬性。

6.Lookup元件

作用：

對Flat File或資料庫根據關聯的條件進行查詢；

傳回符合條件的值，否則為空；

類似于SQL中的select子查詢。

用法：建立Lookup的時候選擇要查詢的表。

有連接配接資料流和不連接配接資料流兩種用法。

連接配接資料流方式：将要關聯查詢的字段從其他轉換元件拖拽過來，然後輕按兩下LKP進行屬性配置。

命名：LKP_Name

類型：Passive/Connected or Unconnected

屬性解釋：

Lookup Sql Override：針對查找值的替換查詢。設定查詢SQL。

Lookup table name：查找源表

Lookup Source Filter：應用于查找記錄的篩選器。

Lookup caching enabled：會話過程中的緩存查找值。啟用緩存，提高效率。

Lookup policy on multiple match：查找轉換發現多個比對時如何處理。

Lookup condition：對查找值應用的篩選器。設定查詢條件。

Connection Information：來自源或目标的查詢記錄

Source Type：資料庫、平面檔案或管道

Tracing Level：會話日志中的詳細資訊量

Lookup cache directory name：查找緩存檔案所在的目錄名稱。

Lookup cache persistent：訓示緩存是持久的還是非持久的。是否使用永久緩存。

Lookup Data Cache Size：查找緩存中用于存儲資料的容量(以位元組為機關)

Lookup Index Cache Size：查找索引緩存中用于存儲資料的容量(以位元組為機關)

Dynamic Lookup Cache：用未發現的或新的查找值更新查找緩存

Synchronize Dynamic Cache：使插入與關系源同步。

Output Old Value On Update：定義在更新動态緩存中的行時是否輸出輸出端口的舊值。

Update Dynamic Cache Condition：僅當此條件為 True 時才更新動态緩存。

Cache File Name Prefix：保留的緩存檔案的檔案名字首。查找将從命名緩存檔案讀取(如果合适)，并保留到命名緩存檔案中

Re-cache from lookup source：首次調用查找執行個體時，從查找源重建查找緩存

Insert Else Update：如果在緩存中找不到該記錄則插入，若找到則将其更新。标志用于動态查找緩存

Update Else Insert：如果在緩存中找到該記錄則将其更新，找不到則插入。标志用于動态查找緩存

Datetime Format：日期時間格式字元串

Thousand Separator：數值資料的千位分隔符

Decimal Separator：數值資料的小數分隔符

Case Sensitive String Comparison：字元串比較(區分大小寫)

Null ordering：确定空值是最高值還是最低值

Sorted Input：定義是否對輸入資料進行排序。

Lookup source is static：在會話運作期間查找源将不會更改。

Pre-build lookup cache：指定是否允許在實際需要之前預先建構查找緩存。

應用範圍：

查詢文本檔案的關聯資料；

查詢資料庫的關聯資料。

6.1 連接配接Lookup與非連接配接Lookup

連接配接Lookup：流入值--》Lookup源--》查詢到的值

查詢條件：列=輸入列

非連接配接Lookup：是否查詢--》Lookup元件--》傳回查詢到的值

查詢條件：IIF(ISNULL(列),:lkp.MYLOOKUP(替換列),列)

6.2 Lookup元件項

L：查詢的資料項

R：傳回值

注意：

如果Lookup表的類型是源表，則盡量使用Joiner元件替換Lookup元件，因為表連接配接比select子查詢效率高。

一個已連接配接的Lookup元件不能再被其他轉換元件調用。

如果查詢表在session運作過程中不會改變，使用Lookup cache persistent屬性可以提高性能；

盡量不要在Lookup Sql Override屬性中使用order by子句，因為cached Lookup元件預設有order by。

例8：從emp、dept表中複制資料到t_emp_8中，t_emp_8中包含部門名稱，使用emp作為源表、dept作為lookup表

例8-2：給例8增加條件，隻找到工資大于3000的職員的部門名

IIF(SAL>3000,:LKP.LKP_DNAME(DEPTNO),'')

錯誤：TM_6190 無法辨別用作查找轉換或存儲過程轉換的$Source或$Target的具有唯一性的關系連接配接或應用程式連接配接。

解決方法：Connection Information屬性由$Source改為O_source即可。

例9：從emp表中複制資料到t_emp_9中，t_emp_9中包含empno,ename,mgr,mgr_name。

作業5：

源表：成績表

目标表：t_grade_lookup(學生名、課程名、成績)

步驟：

源表(成績表)通過兩個Lookup元件将學号轉為學生名、課程号轉為課程名，最後儲存到目标表。

7.Aggregator 元件

作用：

執行分組聚合運算，對資料集進行聚合；

聚合分為：sum、avg、count、max、min

用法：建立好Aggregator 元件後，将需要用的字段從其他轉換元件中拖拽到AGG中，

輕按兩下AGG，

在端口頁中每個端口都有group by選項，

勾上需要分組的列，新增端口，編寫聚合表達式，實作分組聚合運算。

命名：AGG_Name

類型：Active/Connected

屬性解釋：

Cache Directory：緩存的彙總值的目錄

Tracing Level：會話日志中的詳細資訊量

Sorted Input：如果選中，則認為到該轉換的輸入已經由 GroupBy 端口排序。參與Group By的字段是否已經過排序。

Aggregator Data Cache Size：彙總器緩存中用于存儲資料的容量(以位元組為機關)。參與聚集彙總的變量和輸出資料項的表達式所占記憶體。

Aggregator Index Cache Size：彙總器索引緩存中用于存儲資料的容量(以位元組為機關)。參與Group By的字段所占用的記憶體。

Transformation Scope：定義對輸入資料執行轉換的範圍

7.1 Aggregator 元件項

Group By：用于分組的列

O：輸出列

Expression:用于書寫彙總函數表達式

7.2 Aggregator 元件函數

sum(待彙總列,IIF(條件,待彙總列,)) 将不符合條件的值預設指派為Null

IIF(條件,sum(待彙總列)) 将不符合條件的值預設指派為0

注意：可使用本地變量

例10：從dept、emp表中複制資料到t_emp_10中，

t_emp_10包括下面列(部門編号、部門名、辦公地點、員勞工數、工資總和)

作業6：

源表：學生表、課程表、成績表

目标表：t_course_Aggregator(課程号、課程名、老師名、平均分、最高分、最低分、學生數、及格人數、不及格人數)

步驟：

作業7：

描述：計算每個職員工資占部門總工資的百分比。

源表：emp表

目标：t_emp_10_2

要點：先擷取部門總工資，再連接配接，最後計算百分比。

8.Rank元件 等級轉換

作用：排序記錄，隻輸出最頂層行或最底層的n個記錄

用法：建立好Rank後，将需要用的記錄從轉換元件拖拽到Rank中，

輕按兩下Rank，對Rank的屬性進行設定。

在端口頁簽選擇分組列和R列(等級列或排序列)，在屬性頁簽設定Top/Bottom屬性和Number of Ranks屬性。

命名：RANK_Name

類型：Active/Connected

屬性解釋：

Cache Directory：緩存的等級值的目錄

Top/Bottom：選擇頂部或底部等級

Number of Ranks：每一等級中的記錄數

Case Sensitive String Comparison：字元串比較(區分大小寫)

Tracing Level：會話日志中的詳細資訊量

Rank Data Cache Size：等級緩存中用于存儲資料的容量(以位元組為機關)

Rank Index Cache Size：等級索引緩存中用于存儲資料的容量(以位元組為機關)

Transformation Scope：定義對輸入資料執行轉換的範圍

注意：可以使用本地變量

例11：

源表:emp

目标表：T_emp_11

功能：從源表emp中取出每個部門工資前3名的資訊到目标表中。

作業8：

源表：學生表、課程表、成績表

目标表：t_course_rank(課程名、學生名、成績)

功能：從源表(學生表、課程表、成績表)中取出每門課程成績前2名的資訊到目标表中。

9.Union元件 資料合并

屬性解釋：

Language：用于開發此自定義轉換的程式設計語言。

Module Identifier：包含過程的 DLL 或共享庫的名稱。

Function Identifier：該過程在子產品中的名稱。

Runtime Location：包含 DLL 或共享庫的位置。

Tracing Level：會話日志中的詳細資訊量

Is Partitionable：如果此轉換不能在多個分區運作，則選擇“否”。

如果此轉換可以在多個分區運作，但那些分區必須在同一個節點上運作，則選擇“本地”。

如果每個分區都可以在一個網格的不同節點上運作，則選擇“跨整個網格”。

Inputs Must Block：指定與轉換關聯的過程是否阻止傳入資料。

在過程代碼不阻止傳入資料時，或在它可以選擇使用阻止算法或非阻止算法時，可以清除此選項。

Is Active：指定此轉換是主動轉換還是被動轉換。

當啟用此選項時，轉換可以為每個輸入行生成 0、1 或更多個輸出行。

否則，它隻可以為每個輸入行生成 0 或 1 個輸出行。

Update Strategy Transformation：指定此轉換是否為輸出行定義更新政策。

Transformation Scope：指定 Informatica 內建服務如何将轉換邏輯應用于傳入資料: 

應用到“行”、“事務”，還是“所有輸入”。對于被動轉換，此屬性為“行”。

Generate Transaction：指定此轉換是否可以生成事務。

Output Is Repeatable：指定輸出資料是否排序:“從不”、“基于輸入順序”或“始終”。對于被動轉換，該屬性為“基于輸入順序”。

Requires Single Thread Per Partition：指定此自定義轉換是否要求每個分區一個線程。

如果選中，此自定義轉換的每個分區将由且僅由一個線程執行。

Output Is Deterministic：在會話運作期間生成同一組輸出資料。

例12：将源表stu_java 和stu_oracle中的資料同步到目标表T_stu中。

源表：stu_java、stu_oracle

create table stu_oracle(

id number(16) primary key,

sname varchar2(20),

birth date 

);

create table stu_java(

id number(16) primary key,

sname varchar2(20),

birth date 

);

insert into stu_oracle (id,sname,birth) values (1,'zhangsan',sysdate-8000);

insert into stu_java (id,sname,birth) values (2,'lisi',sysdate-7800);

目标表：T_stu

create table t_stu(

id number(16) primary key,

sname varchar2(20),

birth date,

major varchar2(10)

);

作業9：

源1：從emp表找職位是CLERK的人員

源2：從emp表找部門編号是30的人員

目标表：T_emp_Union

功能：因為7900人員同時滿足職位是CLERK和部門編号是30，是以會重複找到兩次；

給目标表不設主鍵，驗證Union元件不去重功能。

10.Sequence Generator 序列号生成器

作用：産生主鍵

用法：建立Sequence Generator後，輕按兩下SEQ，對SEQ的屬性進行設定。

命名：SEQ_Name

類型：Passive/Connected

屬性解釋：

Start Value：起始值

Increment By：步長，向目前值添加了增量

End Value：最大值

Current Value：目前值

Cycle：循環使用。如已達到最大值，則重置為起始值

Number of Cached Values：每次會話運作生成的值的數量

Reset：每次會話運作都以目前值重新啟動該序列

Tracing Level：會話日志中的詳細資訊量

注意：

Sequence Generator隻有兩個輸出端口，沒有輸入端口；

如果該SEQ設定為可以重複使用時，不能選擇Reset選項。

例13：

源表：emp表、emp檔案

目标表:t_emp_13

注意：emp表和emp檔案中原來的主鍵資料是重複的，目标表需要重新設定主鍵字段，并用Sequence Generator生成主鍵資訊。

作業10：

源表：dept表、dept檔案

目标表：T_dept_Union

功能：dept表和dept檔案中原來的主鍵資料是重複的，目标表需要重新設定主鍵字段，并用Sequence Generator生成主鍵資訊。

11.Stored Procedure

作用：調用一個存儲過程

用法：建立Stored Procedure的時候連接配接資料庫選擇必要的存儲過程。

SP有連接配接和不連接配接到資料流兩種用法。

連接配接到資料流方式跟一般的轉換元件類似，通過拖拽端口來實作資料流。

不連接配接到資料流分為表達式調用和Pre-/Post session兩種方式。

表達式調用方式中SP被Mapping中另一個轉換元件中的表達式調用。

Pre-/Post session方式就是在session的屬性中配置SP的運作時間，具體如下：

Source Pre-load ：session從源表查詢資料之前調用。

Source Post-load ：session從源表查詢資料之後調用。

Target Pre-load ：session将資料插入目标表之前調用。

Target Post-load ：session将資料插入目标表之後調用。

命名：SP_Name

類型：Passive/Connected or Unconnected

屬性解釋：

Stored Procedure Name：存儲過程名稱

Connection Information：調用源或目标中的過程

Call Text：調用文法，包括參數

Stored Procedure Type：會話期間調用該過程的時間

Execution Order：內建服務調用該過程的順序

Tracing Level：會話日志中的詳細資訊量

Subsecond Precision：日期時間輸出端口的精度，例如，毫秒對應的值為 3

Output is Repeatable：指定輸出資料是否基于輸入順序進行排序。對于被動轉換，該屬性為“基于輸入順序”。

Output is Deterministic：指定是否為相同的輸入提供相同的輸出

注意：

例14：從dept、emp表中複制資料到t_emp_14中，

t_emp_14包括下面列(部門編号、部門名、辦公地點、員勞工數、工資總和)

要求：先編寫一個存儲過程，再調用過程來實作資料同步

create table t_emp_14 

(

deptno number(2) not null primary key,

dname varchar2(14),

loc varchar2(13),

sum_sal number(15,2) ,

e_cnt number(15,2) 

);

create or replace procedure proc_dept_all

is 

v_sal number(15,2);

v_cnt number(15);

begin

-- for rs in (select * from scott.dept where exists (select 1 from scott.emp where emp.deptno=dept.deptno)) loop 

for rs in (select * from scott.dept where deptno in (select deptno from scott.emp)) loop 

select count(1) into v_cnt from t_emp_14 where deptno=rs.deptno;

if v_cnt=0 then

insert into t_emp_14 (deptno,dname,loc) values (rs.deptno,rs.dname,rs.loc);

end if;

end loop;

for rs in (select * from t_emp_14) loop

select sum(sal),count(1) into v_sal,v_cnt from scott.emp where deptno=rs.deptno;

update t_emp_14 set sum_sal=v_sal,e_cnt=v_cnt where deptno=rs.deptno;

end loop;

end;

/

show err;

create or replace function func_emp14_sal(p_deptno number) return number

is 

v_sal number(15,2);

begin

select sum(sal) into v_sal from scott.emp where deptno=p_deptno;

return v_sal;

end;

/

show err;

create or replace function func_emp14_cnt(p_deptno number) return number

is 

v_cnt number(15,2);

begin

select count(1) into v_cnt from scott.emp where deptno=p_deptno;

return v_cnt;

end;

/

show err;

12.Update Strategy 元件

作用：

對流過元件的每一條記錄賦一個操作标志（插入、删除、更新、忽略）；

根據操作标志對目标關系型資料庫表生成sql操作；

操作标志有DD_INSERT、DD_DELETE、DD_UPDATE、DD_REJECT

用法：

建立好Update Strategy後，将需要的字段從轉換元件拖拽到US，輕按兩下US，對US的屬性進行設定。

建立LOOKUP元件選擇目标表，根據源表的主鍵去目标表查詢，如果查不到資料，就給目标表新增；

如果查到資料就判斷源表和目标表的資料是否相同(比較所有字段)，如果相同就放棄，不相同則更新目标表。

操作：

建立LOOKUP元件後會自動加載目标表所有字段（删除不需要用到的字段），

再從SQ元件中拖拽源表主鍵字段到LOOKUP元件，并設定關聯條件；

将SQ元件源表所有字段拖拽到Update Strategy 元件中，

再将LOOKUP元件中目标表所需要用到的字段拖拽到Update Strategy 元件中，并給字段名增加_T(用以表示目标的字段)；

設定Update Strategy Expression屬性為：

IIF(isnull(EMPNO_T),dd_insert,IIF(EMPNO=EMPNO_T and SAL=SAL_T,dd_reject,dd_update))

将Update Strategy 元件中源表的字段拖拽至目标表。

檢查并修改任務屬性Treat source rows as=Data Driven。

命名：UPD_Name

類型：Active/Connected

屬性解釋：

Update Strategy Expression：對記錄标記，标記要插入、删除、更新或拒絕的記錄的表達式。

Forward Rejected Rows：将被拒絕的行轉發到下一轉換。記錄是在目前丢棄還是到下個元件丢棄。

Tracing Level：會話日志中的詳細資訊量

應用範圍：

資料增量更新；

對目标編輯。

注意：

關鍵是Update Strategy Expression屬性的設定，使用IIF或decode函數對記錄的每一行進行判斷，

為每一行賦予DD_INSERT、DD_DELETE、DD_UPDATE、DD_REJECT（對應值為0、1、2、3）中的一個值，

表明該行在資料庫中将進行的（增、删、改、忽略）某種操作。

mapping中有US時，mapping對應的session在配置屬性時必須把Treat rows屬性設定為Data Driven，否則US無效。

例15：從emp表中複制資料到目标表t_emp_15中，

然後對emp表進行增、删、改操作，并将增、删、改操作影響的行更新到目标表t_emp_15中。

connect bi_target/bi_target;

create table t_emp_15 as select * from scott.emp;

select * from t_emp_15;

connect scott/123;

insert into emp (empno,ename) values (1112,'abcd');

update scott.emp set sal=20000 where empno=7839;

--delete emp where empno=7900;

--update emp set sal=8000 where empno=7934;

Update Strategy屬性設定：

IIF(條件，真，假)

IIF(isnull(EMPNO_T),dd_insert,IIF(EMPNO=EMPNO_T and SAL=SAL_T,dd_reject,dd_update))

IIF(ISNULL(EMPNO_T),DD_INSERT,IIF(EMPNO=EMPNO_T AND SAL=SAL_T,DD_REJECT,DD_UPDATE))

作業11：

源表：dept表

目标表：T_dept_Update

功能：實作對源表dept的新增、修改，同步複制到目标表T_dept_Update的功能。

IIF(isnull(DEPTNO),dd_insert,iif(DEPTNO=DEPTNO1 and DNAME=DNAME1 and LOC=LOC1,dd_reject,dd_update))

例16：從dept_temp表中複制資料到目标表t_dept_16中

create table dept_temp as select * from dept;

alter table dept_temp add action varchar2(10);

alter table dept_temp add action_date date;

alter table dept_temp add constraint pk_dept_temp primary key (deptno);

update dept_temp set action='insert',action_date=sysdate-20;

第一次抽取：

IIF(ACTION_DATE<TO_DATE('20180301','yyyymmdd'),DECODE(ACTION,'update',dd_update,'delete',dd_delete,dd_insert),dd_reject)

--10删除

update dept_temp set action='delete',action_date=sysdate where deptno=10;

--20修改

update dept_temp set dname='123',action='update',action_date=sysdate where deptno=20;

--50新增

insert into dept_temp values(50,'bumen','loc','insert',sysdate);

後續抽取：

IIF(ACTION_DATE>=TO_DATE('20180301','yyyymmdd'),DECODE(ACTION,'update',dd_update,'delete',dd_delete,dd_insert),dd_reject)

13.Normalizer元件

作用：格式化記錄，将清單轉換為行表。

用法：Occurs=要合并的列個數，将GCID_*轉換成對應的分類名

命名：NRM_Name

類型：Active/Connected

屬性解釋：

注意：

例20：

源表：工資表

create table emp_sal

(

empno number(10) not null primary key,

base_sal number(10,2) ,

jixiao_sal number(10,2) ,

gongling_sal number(10,2) ,

quanqin_sal number(10,2) 

);

insert into emp_sal values (111,2000,1800,100,300);

insert into emp_sal values (112,5000,0,500,300);

目标表：收入表

create table t_emp_20 

(

empno number(10) not null primary key,

income_type varchar2(20) ,

sal_amount number(10,2) 

);

14.Transaction Control元件

作用：控制事務的送出或回退。

Transaction Control元件是一個事務産生器，它在Mapping中定義或者重新定義事務的邊界。

Transaction Control元件同時清除了任何來自上遊的事務。

用法：輕按兩下Transaction Control元件，在屬性頁簽設定Transaction Control Condition的值，來控制事務的送出或回退。

TC_CONTINUE_TRANSACTION 不進行任何事物變更

TC_COMMIT_BEFORE 送出目前事務，開啟新事物，目前行進入新事物

TC_COMMIT_AFTER 送出目前事務，開啟新事物，目前行留在本事物

TC_ROLLBACK_BEFORE 回退目前事務，開啟新事物，目前行進入新事物

TC_ROLLBACK_AFTER 回退目前事務，開啟新事物，目前行留在本事物

命名：TC_Name

類型：Active/Connected

屬性解釋：

Transaction Control Condition：事務控制表達式

Tracing Level：會話日志中的詳細資訊量

注意：

例21：使用mapping動态分發檔案

以Dept為例，按照LOC列将對應的資料寫入不同的檔案中。

源--》SQ--》Sorter--》f(x)--》TX--》目标

目标中增加FileName列；

Sorter中對LOC列進行排序；

f(x)中定義本地變量：

LAST_VALUE 上次取值 表達式指派為CURRENT_VALUE

CURRENT_VALUE 本次取值 表達式指派為LOC

f(x)中定義輸出端口：

var_control 是否是同一個部門辨別 表達式指派為IIF(LAST_VALUE=CURRENT_VALUE,1,0)

FileName 檔案名 表達式指派為LOC.dat

TX中使用事務控制表達式：IIF(var_control=1,TC_CONTINUE_TRANSCATION,TC_COMMIT_BEFORE)

15.SQL Transformation元件

作用：使用mapping來執行SQL.

腳本模式：Script mode

查詢模式：query mode，又分為靜态查詢和動态查詢

用法：

原為檔案，

拖入SQL Transformation元件，選擇靜态查詢，

查詢sql：select

注意：

？參數？

~動态變量~

1.6 JAVA Transformation

作用：使用Java代碼來處理informatica不能完成的工作。

使用Java來自定義轉換元件。

17.Custom Transformation

作用：除了info提供的轉換元件外，使用者可以使用Custom Transformation開發自己的特有的轉換元件。

使用C/C++來自定義元件。

18.Http Transformation

作用：從HTTP server擷取資料；更新HTTP server上的資料。

19.XML Source Qualifier元件

作用：根據查詢SQL從XML檔案中查出所要的記錄

用法：

命名：XSQ_Name

類型：Passive/Connected

屬性解釋：

注意：

20.ERP Source Qualifier元件

作用：根據查詢SQL從ERP檔案中查出所要的記錄

用法：

命名：ESQ_Name

類型：Active/Connected

屬性解釋：

注意：