在plsql開發中，會涉及到一些大數據量表的數據處理，如將某記錄數超億的表的記錄經過處理轉換插入到另外一張或幾張表。 常規的操作方法固然可以實現，但時間、磁盤IO、redo日志等等都非常大。oracle 提供了一種高級函數，可以將這種數據處理的性能提升到極限
在plsql開發中，會涉及到一些大數據量表的數據處理，如將某記錄數超億的表的記錄經過處理轉換插入到另外一張或幾張表。
常規的操作方法固然可以實現，但時間、磁盤IO、redo日志等等都非常大。Oracle 提供了一種高級函數，可以將這種數據處理的性能提升到極限。這種函數稱為管道函數。
在實際項目中，管道函數會和表函數、數據流函數（即表函數和CURSOR結合）、數據集合、并行度一起使用，達到大數據處理的性能頂峰。
–
下面是一個例子，將表t_ss_normal的記錄插入到表t_target中，插入過程中有部分轉換操作。
我分成四個方法來實現這個數據處理操作。
第一個方法，也是最常規的方法，代碼如下：
1.create table T_SS_NORMAL??
2.(??
3.? owner????????? VARCHAR2(30),??
4.? object_name??? VARCHAR2(128),??
5.? subobject_name VARCHAR2(30),??
6.? object_id????? NUMBER,??
7.? data_object_id NUMBER,??
8.? object_type??? VARCHAR2(19),??
9.? created??????? DATE,?? 10.? last_ddl_time? DATE,?? 11.? timestamp????? VARCHAR2(19),?? 12.? status???????? VARCHAR2(7),??
13.? temporary????? VARCHAR2(1),?? 14.? generated????? VARCHAR2(1),??
15.? secondary????? VARCHAR2(1)??
16.);??
17./??
18.?
19.create table T_TARGET?? 20.(??
21.? owner?????? VARCHAR2(30),??
22.? object_name VARCHAR2(128),??
23.? comm??????? VARCHAR2(10)??
24.);?
這是源表和目標表的表結構?，F在源表有200W條，其數據來自dba_objects視圖。
1.create or replace package pkg_test is?
2.? procedure load_target_normal;?? 3.end pkg_test;?? 4.?
5.create or replace package body pkg_test is? 6.? procedure load_target_normal is? 7.? begin???? 8.??? insert into t_target (owner, object_name, comm)?? 9.????? select owner, object_name, ‘xxx’ from t_ss_normal;???? 10.??? commit;???? 11.? end;?? 12.begin? 13.? null;?? 14.end pkg_test;
一個insert into select語句搞定這個數據處理，簡單。
一個insert into select語句搞定這個數據處理，簡單。
1.create type obj_target as object(??
2.owner VARCHAR2(30), object_name VARCHAR2(128), comm varchar2(10)??
3.);??
4./??
5.create or replace type typ_array_target as table of obj_target;?? 6./??
7.?
8.create or replace package pkg_test is? 9.?
10.? function pipe_target(p_source_data in sys_refcursor) return typ_array_target?? 11.??? pipelined;??
12.?
13.? procedure load_target;?? 14.end pkg_test;? 首先創建兩個自定義的類型。obj_target的定義和t_target的表結構一致，用于存儲每一條目標表記錄。typ_array_target用于管道函數的返回值。
接著定義一個管道函數。
普通函數的結尾加一個pipelined關鍵字，就是管道函數。這個函數的返回參數類型為集合，這是為了使其能作為表函數使用。表函數就是在from子句中以table(v_resultset)調用的，v_resultset就是一個集合類型的參數。
最后定義一個調用存儲過程。
在包體中定義該管道函數和調用存儲過程。管道函數pipe_target的傳入參數一個sys_refcursor類型。這是一個游標，可以理解為使用select * from table才能得到的結果集。
你也可以不用這個傳入的游標，取而代之，在函數中定義一個游標，也一樣使用。
1. function pipe_target(p_source_data in sys_refcursor) return typ_array_target??
2.??? pipelined is? 3.??? r_target_data obj_target := obj_target(null, null, null);?? 4.??? r_source_data t_ss%rowtype;??
5.?
6. begin? 7.????
8.??? loop??
9.????? fetch p_source_data?? 10.??????? into r_source_data;?? 11.????? exit when p_source_data%notfound;?????? 12.????????
13.????? r_target_data.owner?????? := r_source_data.owner;??
14.????? r_target_data.object_name := r_source_data.object_name;??
15.????? r_target_data.comm??????? := ‘xxx’;?????? 16.????? pipe row(r_target_data);??
17.??????
18.??? end loop;?? 19.????
20.??? close p_source_data;?? 21.??? return;?? 22.????
23.? end;?? 24.?
25.? procedure load_target is? 26.? begin???? 27.??? insert into t_target?? 28.????? (owner, object_name, comm)??
29.????? select owner, object_name, comm?? 30.??????? from table(pipe_target(cursor(select * from t_ss_normal)));???? 31.??? commit;???? 32.? end;?? 33.???
