实例代码由浅入深，实例4以后的代码如果看不懂不勉强，输入高级开发范畴，需要了解数据仓库维度表，事实表和索引组织表的概念**
1.还是先来看一个案例，此案例并无实际意义,我基于table()函数生成了一个虚拟表，该函数括号中可以是list集合、可以是数组、可以是变量，可以
是dbms API系统包，可以被调用，也可以调用其它，
案例1：
–创建自定义类型
CREATE OR REPLACE TYPE TYPE_VIRTUAL AS OBJECT(
ORA600_ID NUMBER ,
DATETIME DATE,
REMARK VARCHAR2(50)
);
–创建备份自定义类型
CREATE OR REPLACE TYPE TYPE_VIRTUAL_BACKUP AS TABLE OF TYPE_VIRTUAL;
–创建自定义函数
CREATE OR REPLACE FUNCTION FUNC_VIRTUAL(P_ARRAY IN NUMBER)
RETURN TYPE_VIRTUAL_BACKUP AS
V_VIRTUAL TYPE_VIRTUAL_BACKUP := TYPE_VIRTUAL_BACKUP();
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
FOR I IN 1 .. P_ARRAY LOOP
V_VIRTUAL.EXTEND();
V_VIRTUAL(V_VIRTUAL.COUNT) := TYPE_VIRTUAL(I,
SYSDATE + I,
‘Description:’ ||
DBMS_RANDOM.STRING(‘B’, 10));
END LOOP;
RETURN V_VIRTUAL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Errors:’ || ‘ ‘ || SQLCODE || ‘Context:’ ||
SQLERRM);
END;
–做一个查询测试
SELECT ORA600_ID,DATETIME
FROM TABLE(FUNC_VIRTUAL(100))
WHERE ORA600_ID BETWEEN 1 AND 10
AND DATETIME >= TO_DATE(‘2016-11-15 15:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND DATETIME <= TO_DATE(‘2016-11-20 15:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);
结论：对于DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL)的用法官方的解释是Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000
案例2：
1）此案例需要用到pipe_row()函数，这个函数具体用法详见官网oracle sql developer pipeline function部分内容，该函数其实很简单，等同于
DBMS_OUTPUT.PUTLINE(),唯一的区别就是DBMS_OUTPUT是将plsql内容一次性返回给客户端，在速度上和打印超过varchar2字符串上线的时候就会比较
慢，然后pipe_rows()是实时的动态传输结果集，无论是在函数和存储过程中调用，该函数必须返回一个List集合或者集合的单个元素。
2）我们通过多个案例了解pipe_row()的具体应用，以下为实例代码
官方给出的语法结构：
CREATE OR REPLACE FUNCTION .
( [IN | OUT | IN OUT] [NOCOPY] )
RETURN
[AUTHID ]
[]
[PARALLEL_ENABLE (PARTITION BY [ (), ANY])] IS
BEGIN
END ;
CREATE TABLE stocktable (
ticker VARCHAR2(4),
open_price NUMBER(10),
close_price NUMBER(10));
INSERT INTO stocktable VALUES (‘ORCL’, 13, 16);
INSERT INTO stocktable VALUES (‘MSFT’, 35, 29);
INSERT INTO stocktable VALUES (‘SUNW’, 7, 11);
COMMIT;
–创建自定义类型
CREATE OR REPLACE TYPE TICKERTYPE AUTHID DEFINER AS OBJECT
(
TICKER VARCHAR2(4),
PRICETYPE VARCHAR2(1),
PRICE NUMBER(10)
)
;
–创建复制自定义类型
CREATE OR REPLACE TYPE TickerTypeSet AS TABLE OF TickerType;
–创建工具包将ref游标参数作为对象调用参数封装起来
CREATE OR REPLACE PACKAGE REFCUR_PKG AUTHID DEFINER IS
TYPE REFCUR_T IS REF CURSOR RETURN STOCKTABLE%ROWTYPE;
END REFCUR_PKG;
–创建函数，传入参数就是工具包的ref游标
CREATE OR REPLACE FUNCTION STOCKPIVOT(P REFCUR_PKG.REFCUR_T)
RETURN TICKERTYPESET AUTHID DEFINER
PIPELINED IS
OUT_REC TICKERTYPE := TICKERTYPE(NULL, NULL, NULL);
IN_REC P%ROWTYPE;
BEGIN
LOOP
FETCH P
INTO IN_REC;
EXIT WHEN P%NOTFOUND;
OUT_REC.TICKER := IN_REC.TICKER;
OUT_REC.PRICETYPE := 'O';
OUT_REC.PRICE := IN_REC.OPEN_PRICE;
PIPE ROW(OUT_REC);
OUT_REC.PRICETYPE := 'C';
OUT_REC.PRICE := IN_REC.CLOSE_PRICE;
PIPE ROW(OUT_REC);END LOOP;
CLOSE P;
RETURN;
END STOCKPIVOT;
–查询存储过程数据字典，可以看到那些对象已经被pipelined
SELECT OBJECT_NAME, PIPELINED, AUTHID FROM USER_PROCEDURES;
–查询table()函数，调用该自定义函数
SELECT * FROM TABLE(STOCKPIVOT(CURSOR (SELECT * FROM STOCKTABLE)));
总结：案例2为案例1的变种，无非就是基于ref动态游标，完成类型结构一次调用，日常开发用的不算太过频繁
案例3：
–创建自定义类型
CREATE OR REPLACE TYPE str_array AS TABLE OF VARCHAR2(10);
–创建函数，用于分割带都逗号的字符串，对于DBMS_UTILITY工具包的用法，心细的小弟会经常看到我用这个工具包取当前系统时间，后期我会整理
–开发文档，统一学习这个包。
CREATE OR REPLACE FUNCTION PTF(STRINGIN VARCHAR2) RETURN STR_ARRAY
PIPELINED IS
I PLS_INTEGER;
STR VARCHAR2(100);
TAB SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
STR := ‘”‘ || REPLACE(STRINGIN, ‘,’, ‘”,”‘) || ‘”‘;
SYS.DBMS_UTILITY.COMMA_TO_TABLE(STR, I, TAB);
FOR J IN 1 .. 5 LOOP
PIPE ROW(TRANSLATE(TAB(J), ‘A”‘, ‘A’));
END LOOP;
RETURN;
END PTF;
–手工输入字符串，调用函数，看结果集是否逗号被分割
SELECT * FROM TABLE(CAST(PTF(‘1001,1002,1003,1004,1005’) AS STR_ARRAY));
关于DBMS_UTILITY工具包，以下有小案例，可以分享一下
DECLARE
T_VARARRAY DBMS_UTILITY.LNAME_ARRAY;
VC_STRINGLIST VARCHAR2(4000);
N_IDX BINARY_INTEGER;
BEGIN
VC_STRINGLIST := ‘dkf,dddl,fewe,klkj’;
DBMS_UTILITY.COMMA_TO_TABLE(VC_STRINGLIST, N_IDX, T_VARARRAY);
DBMS_OUTPUT.PUT_LINE(‘Total Num : ‘ || TO_CHAR(N_IDX));
FOR I IN 1 .. N_IDX LOOP
DBMS_OUTPUT.PUT_LINE(T_VARARRAY(I));
T_VARARRAY(I) := ‘[‘ || T_VARARRAY(I) || ‘]’;
END LOOP;
DBMS_UTILITY.TABLE_TO_COMMA(T_VARARRAY, N_IDX, VC_STRINGLIST);
DBMS_OUTPUT.PUT_LINE(” || VC_STRINGLIST);
END;
案例4：
–创建对象类型
CREATE OR REPLACE TYPE msgType AS TABLE OF VARCHAR2(60);
/*
1.创建自定义函数用于打印当前systimestamp分别得三段代码，注意此部分需要调整array参数，为了让大家了解客户端和服务端交互的逻辑读
我特意加了这个参数的相关案例在里面，注意arraysize参数在sqlplus层面设置，默认是15，有效范围为1-5000，定义了一次返回到客户端的行数
可以有效的降低每次扫描数据块逻辑读的次数，当然这个参数也不是越大越好
2.arraysize定义了一次返回到客户端的行数，当扫描了arraysize 行后，停止扫描，返回数据，然后继续扫描。这个过程就是统计信息中的
SQL*Net roundtrips to/from client。因为arraysize 默认是15行，那么就有一个问题，因为我们一个block 中的记录数一般都会超过15行，
所以如果按照15行扫描一次，那么每次扫描要多扫描一个数据块，一个数据块也可能就会重复扫描多次。
*/
CREATE OR REPLACE FUNCTION msgOutStream RETURN msgType
PIPELINED AUTHID CURRENT_USER AS
BEGIN
PIPE ROW(‘start run ‘ || sysTimestamp);
FOR i IN 1 .. 10 LOOP
PIPE ROW(‘output ‘ || TO_CHAR(i) || ‘ ‘ || sysTimestamp);
dbms_lock.sleep(1);
PIPE ROW(‘output ‘ || TO_CHAR(i) || ‘ completed’);
END LOOP;
RETURN;
END;
/
SELECT * FROM TABLE(msgOutStream);
SET ARRAYSIZE 1
SELECT * FROM TABLE(msgOutStream);
案例5：
–创建对象类型，
CREATE OR REPLACE TYPE name_t AS TABLE OF VARCHAR2(30);
–创建自定义函数，利用函数打印数据字典
CREATE OR REPLACE FUNCTION DEPENDENCIES(PKG_NAME IN VARCHAR2) RETURN NAME_T
AUTHID CURRENT_USER
PIPELINED AS
CURSOR DCUR IS
SELECT NAME
FROM DBA_DEPENDENCIES
WHERE REFERENCED_NAME = PKG_NAME
AND NAME <> PKG_NAME
AND NAME <> ‘STANDARD’
UNION
SELECT REFERENCED_NAME
FROM DBA_DEPENDENCIES
WHERE NAME = PKG_NAME
AND REFERENCED_NAME <> PKG_NAME
AND REFERENCED_NAME <> ‘STANDARD’;
BEGIN
FOR DREC IN DCUR LOOP
PIPE ROW(DREC.NAME);
END LOOP;
RETURN;
END DEPENDENCIES;
–查询table()调用自定义函数后打印的内容
SELECT * FROM TABLE(CAST(dependencies(‘OWA_OPT_LOCK’) AS name_t));
案例6：
CREATE OR REPLACE PACKAGE pkg_test AUTHID CURRENT_USER IS
TYPE rt_Src IS RECORD(id NUMBER, col1 VARCHAR2(100));
TYPE rc_Src IS REF CURSOR RETURN rt_Src;
TYPE rt_Out IS RECORD(id NUMBER, col1 VARCHAR2(100), sid NUMBER);
TYPE tt_Out IS TABLE OF rt_Out;
FUNCTION f_Pipe_Any(airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY ANY);
FUNCTION f_Pipe_Hash(airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY HASH(id));
FUNCTION f_Pipe_Range(airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY RANGE(id));
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_test IS
FUNCTION f_Pipe_Any(airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY ANY) IS
lr_Src rt_Src;
lr_Out rt_Out;
BEGIN
LOOP
FETCH airc_Src INTO lr_Src;
EXIT WHEN airc_Src%NOTFOUND;
SELECT sid
INTO lr_Out.sid
FROM v$mystat
WHERE ROWNUM = 1;
lr_Out.id := lr_Src.id;
lr_Out.col1 := lr_Src.col1;
PIPE ROW(lr_Out);END LOOP;
CLOSE airc_Src;
END;
FUNCTION f_Pipe_Hash (airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY HASH(id)) IS
lr_Src rt_Src;
lr_Out rt_Out;
BEGIN
LOOP
FETCH airc_Src INTO lr_Src;
EXIT WHEN airc_Src%NOTFOUND;
SELECT sid
INTO lr_Out.sid
FROM v$mystat
WHERE ROWNUM = 1;
lr_Out.id := lr_Src.id;
lr_Out.col1 := lr_Src.col1;
PIPE ROW(lr_Out);END LOOP;
CLOSE airc_Src;
END;
FUNCTION f_Pipe_Range (airc_Src IN rc_Src) RETURN tt_Out PIPELINED
PARALLEL_ENABLE(PARTITION airc_Src BY RANGE(id)) IS
lr_Src rt_Src;
lr_Out rt_Out;
BEGIN
LOOP
FETCH airc_Src INTO lr_Src;
EXIT WHEN airc_Src%NOTFOUND;
SELECT sid
INTO lr_Out.sid
FROM v$mystat
WHERE ROWNUM = 1;
lr_Out.id := lr_Src.id;
lr_Out.col1 := lr_Src.col1;
PIPE ROW(lr_Out);END LOOP;
CLOSE airc_Src;
END;
END;
/
CREATE TABLE dt_src (
id NUMBER,
col1 VARCHAR2(100));
INSERT INTO dt_src
SELECT ROWNUM, TO_CHAR(ROWNUM)
FROM dual
CONNECT BY LEVEL <= 100000;
CREATE TABLE dt_out (
id NUMBER,
col1 VARCHAR2(100),
sid NUMBER,
src_proc VARCHAR2(32));
set autotrace trace stat explain
INSERT INTO dt_out
SELECT id, col1, sid, ‘ANY’
FROM TABLE (pkg_test.f_Pipe_Any(CURSOR(SELECT –+parallel(dt_src)
id, col1 FROM dt_src)));
INSERT INTO dt_out
SELECT id, col1, sid, ‘HASH’
FROM TABLE(pkg_test.f_Pipe_Hash(CURSOR(SELECT –+parallel(dt_src)
id, col1 FROM dt_src)));
INSERT INTO dt_out
SELECT id, col1, sid, ‘RANGE’
FROM TABLE (pkg_test.f_Pipe_Range(CURSOR(SELECT –+parallel(dt_src)
id, col1 FROM dt_src)));
set autotrace off
SELECT COUNT(*), sid, src_proc
FROM dt_out
GROUP BY sid, src_proc
ORDER BY sid, src_proc;
案例7：
我们利用Oracle的pipelined函数提升数据输出性能，此部分为选修，了解即可。
1.之前我介绍过collect函数不仅完成对象分组，同时可以打印基于分组的内容信息，其实pipelined函数和collect函数有异曲同工的功能点
2.pipelined函数处理的数据，是以管道的方式，同时该函数可以并发操作数据库对象，
3.pipelined函数可以很容易将存储过程转换成用bulk操作的行
4.以下为案例7实例代码：
–创建对象类型
CREATE OR REPLACE TYPE TYPE_DEMO7 AS TABLE OF NUMBER;
/创建自定义函数，注意P_ROWS_IN为传入参数，基于pipelined我们可以写缓存的方式打印 少量pipelined输出的结果内容/
CREATE OR REPLACE FUNCTION FUNC_ROW_GENERATOR(P_ROWS_IN IN PLS_INTEGER)
RETURN TYPE_DEMO7
PIPELINED IS
BEGIN
FOR I IN 1 .. P_ROWS_IN LOOP
PIPE ROW(I);
END LOOP;
RETURN;
END;
/*
1.使用pipelined必须提前定义对象类型，例如type_demo7中的例子
2.在函数主体部分的PIPE ROW是将一个单行记录写入到collection中，
记录中所有字段的类型必须和collection类型中所有字段匹配。
3.在函数主体部分的“return“的值是一个空值，而不是有任何符合collection类的值
*/
–查询调用pipeline函数后返回的结果集内容
SELECT * FROM TABLE(FUNC_ROW_GENERATOR(10000000000))–打印一百亿pipelined输出内容
/将pipelined函数row_generator放到一个“TABLE”操作符中，虚拟成一个数据源， 类似表或视图。这里虚拟表只有一个字段，名称“COLUMN_VALUE“是其默认值。更复杂的输出则需要将collection定义得更复杂些， 使用到object或者record。/
/我是分割线/
–下面我们来做个比较
–创建自动以函数
CREATE OR REPLACE FUNCTION FUNC_TABLE RETURN TYPE_DEMO7 AS
NT TYPE_DEMO7 := TYPE_DEMO7();
BEGIN
FOR I IN 1 .. 500000 LOOP
IF (MOD(I, 10000) = 0) THEN
NT.EXTEND;
NT(NT.LAST) := I;
END IF;
END LOOP;
RETURN NT; –<– return whole collection
END FUNC_TABLE;
–创建pipelined调用以上函数
CREATE OR REPLACE FUNCTION FUNC_PIPELINED RETURN TYPE_DEMO7
PIPELINED AS
BEGIN
FOR I IN 1 .. 500000 LOOP
IF (MOD(I, 10000) = 0) THEN
PIPE ROW(I); –<– send row to consumer
END IF;
END LOOP;
RETURN;
END FUNC_PIPELINED;
–在建立一个函数返回当前系统时间
CREATE FUNCTION FUNC_GET_TIME RETURN TIMESTAMP IS
BEGIN
RETURN LOCALTIMESTAMP;
END FUNC_GET_TIME;
–同时测试以上调用两个函数的查询速度
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ‘HH24:MI:SS.FF3’;
SELECT DBMS_UTILITY.get_time AS ts FROM DUAL;–18776725
SELECT COLUMN_VALUE, DBMS_UTILITY.get_time AS TS FROM TABLE(FUNC_TABLE);
SELECT COLUMN_VALUE,DBMS_UTILITY.get_time AS TS FROM TABLE(FUNC_PIPELINED);
实例8：
1.目前为止我们已经掌握：
1）bulk+collect+forall的批量处理方式
2）dbms_parallel_execute工具包的基于主键基于rowid更新的快速处理方式，
3）first_rows快速定位前N行利用rowid快速删除当前物理表数据三种技术
2.那么对于千万级别，亿以上级别数据量，可能以上三种方式处理速度上就比较慢了。常规的操作方法固然可以实现，但时间、磁盘IO、redo日志等等都非常大。
Oracle 提供了一种高级函数，可以将这种数据处理的性能提升到极限。这种函数称为管道函数
3.在实际项目中，管道函数会和表函数、数据流函数（即表函数和CURSOR结合）、数据集合、并行度一起使用，达到大数据处理的性能顶峰
4.以上为实例代码，看一下管道函数的威力：
–建立源表
create table T_OBJECTS
(
owner VARCHAR2(30),
object_name VARCHAR2(128),
subobject_name VARCHAR2(30),
object_id NUMBER,
data_object_id NUMBER,
object_type VARCHAR2(30),
created DATE,
last_ddl_time DATE,
timestamp VARCHAR2(19),
status VARCHAR2(7),
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1)
);
–插入测试数据，多插入几遍。
ALTER TABLE T_OBJECTS NOLOGGING ;
INSERT /*+ APPEND */
INTO T_OBJECTS NOLOGGING
SELECT TT.OWNER,
TT.OBJECT_NAME,
TT.SUBOBJECT_NAME,
TT.OBJECT_ID,
TT.DATA_OBJECT_ID,
TT.OBJECT_TYPE,
TT.CREATED,
TT.LAST_DDL_TIME,
TT.TIMESTAMP,
TT.STATUS,
TT.TEMPORARY,
TT.GENERATED,
TT.SECONDARY
FROM DBA_OBJECTS TT;
COMMIT;
INSERT /*+ APPEND */
INTO T_OBJECTS NOLOGGING
SELECT TT.OWNER,
TT.OBJECT_NAME,
TT.SUBOBJECT_NAME,
TT.OBJECT_ID,
TT.DATA_OBJECT_ID,
TT.OBJECT_TYPE,
TT.CREATED,
TT.LAST_DDL_TIME,
TT.TIMESTAMP,
TT.STATUS,
TT.TEMPORARY,
TT.GENERATED,
TT.SECONDARY
FROM DBA_OBJECTS TT;
COMMIT;
INSERT /*+ APPEND */
INTO T_OBJECTS NOLOGGING
SELECT TT.OWNER,
TT.OBJECT_NAME,
TT.SUBOBJECT_NAME,
TT.OBJECT_ID,
TT.DATA_OBJECT_ID,
TT.OBJECT_TYPE,
TT.CREATED,
TT.LAST_DDL_TIME,
TT.TIMESTAMP,
TT.STATUS,
TT.TEMPORARY,
TT.GENERATED,
TT.SECONDARY
FROM DBA_OBJECTS TT;
COMMIT;
INSERT /*+ APPEND */
INTO T_OBJECTS NOLOGGING
SELECT TT.OWNER,
TT.OBJECT_NAME,
TT.SUBOBJECT_NAME,
TT.OBJECT_ID,
TT.DATA_OBJECT_ID,
TT.OBJECT_TYPE,
TT.CREATED,
TT.LAST_DDL_TIME,
TT.TIMESTAMP,
TT.STATUS,
TT.TEMPORARY,
TT.GENERATED,
TT.SECONDARY
FROM DBA_OBJECTS TT;
COMMIT;
–建立目标表
create table T_TARGET
(
owner VARCHAR2(30),
object_name VARCHAR2(128),
comm VARCHAR2(10)
);
–普通插入操作工具包
CREATE OR REPLACE PACKAGE PKG_TEST IS
PROCEDURE LOAD_TARGET_NORMAL;
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
PROCEDURE LOAD_TARGET_NORMAL IS
BEGIN
INSERT INTO T_TARGET(OWNER, OBJECT_NAME, COMM) SELECT OWNER, OBJECT_NAME, ‘xxx’ FROM T_OBJECTS; COMMIT;
END;
BEGIN
NULL;
END PKG_TEST;
–测试执行以上代码，多执行几遍插入T_OBJECTS源表的记录数
–普通PIPE写缓存插入方式
CREATE TYPE OBJ_TARGET AS OBJECT
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
COMM VARCHAR2(10)
)
;
/ CREATE OR REPLACE TYPE TYP_ARRAY_TARGET AS TABLE OF OBJ_TARGET;
/
CREATE OR REPLACE PACKAGE PKG_TEST1 IS
FUNCTION PIPE_TARGET(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED;
PROCEDURE LOAD_TARGET;
END PKG_TEST1;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST1 IS
FUNCTION PIPE_TARGET(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED IS
R_TARGET_DATA OBJ_TARGET := OBJ_TARGET(NULL, NULL, NULL);
R_SOURCE_DATA T_OBJECTS%ROWTYPE;
BEGIN
LOOP
FETCH P_SOURCE_DATA
INTO R_SOURCE_DATA;
EXIT WHEN P_SOURCE_DATA%NOTFOUND;
R_TARGET_DATA.OWNER := R_SOURCE_DATA.OWNER;
R_TARGET_DATA.OBJECT_NAME := R_SOURCE_DATA.OBJECT_NAME;
R_TARGET_DATA.COMM := ‘xxx’;
PIPE ROW(R_TARGET_DATA);
END LOOP;
CLOSE P_SOURCE_DATA;
RETURN;
END;
PROCEDURE LOAD_TARGET IS
BEGIN
INSERT INTO T_TARGET
(OWNER, OBJECT_NAME, COMM)
SELECT OWNER, OBJECT_NAME, COMM
FROM TABLE(PIPE_TARGET(CURSOR (SELECT * FROM T_OBJECTS)));
COMMIT;
END;
END;
TRUNCATE TABLE T_TARGET;
–PIPE管道+BULK COLLECT方式实现插入
CREATE OR REPLACE PACKAGE PKG_TEST2 IS
FUNCTION PIPE_TARGET_ARRAY(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED;
PROCEDURE LOAD_TARGET_ARRAY;
END PKG_TEST2;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST2 IS
FUNCTION PIPE_TARGET_ARRAY(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED IS
R_TARGET_DATA OBJ_TARGET := OBJ_TARGET(NULL, NULL, NULL);
TYPE TYP_SOURCE_DATA IS TABLE OF T_OBJECTS%ROWTYPE INDEX BY PLS_INTEGER;
AA_SOURCE_DATA TYP_SOURCE_DATA;
BEGIN
LOOP
FETCH P_SOURCE_DATA BULK COLLECT
INTO AA_SOURCE_DATA;
EXIT WHEN AA_SOURCE_DATA.COUNT = 0;
FOR I IN 1 .. AA_SOURCE_DATA.COUNT LOOP
R_TARGET_DATA.OWNER := AA_SOURCE_DATA(I).OWNER;
R_TARGET_DATA.OBJECT_NAME := AA_SOURCE_DATA(I).OBJECT_NAME;
R_TARGET_DATA.COMM := ‘xxx’;
PIPE ROW(R_TARGET_DATA);
END LOOP;
END LOOP;
CLOSE P_SOURCE_DATA;
RETURN;
END;
PROCEDURE LOAD_TARGET_ARRAY IS
BEGIN
INSERT INTO T_TARGET
(OWNER, OBJECT_NAME, COMM)
SELECT OWNER, OBJECT_NAME, COMM
FROM TABLE(PIPE_TARGET_ARRAY(CURSOR (SELECT * FROM T_OBJECTS)));
COMMIT;
END;
END;
–PIPE+PARALLEL方式插入
CREATE OR REPLACE PACKAGE PKG_TEST3 IS
FUNCTION PIPE_TARGET_PARALLEL(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED
PARALLEL_ENABLE(PARTITION P_SOURCE_DATA BY ANY);
PROCEDURE LOAD_TARGET_PARALLEL;
END PKG_TEST3;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST3 IS
FUNCTION PIPE_TARGET_PARALLEL(P_SOURCE_DATA IN SYS_REFCURSOR)
RETURN TYP_ARRAY_TARGET
PIPELINED
PARALLEL_ENABLE(PARTITION P_SOURCE_DATA BY ANY) IS
R_TARGET_DATA OBJ_TARGET := OBJ_TARGET(NULL, NULL, NULL);
TYPE TYP_SOURCE_DATA IS TABLE OF T_OBJECTS%ROWTYPE INDEX BY PLS_INTEGER;
AA_SOURCE_DATA TYP_SOURCE_DATA;BEGIN
LOOP
FETCH P_SOURCE_DATA BULK COLLECT
INTO AA_SOURCE_DATA;
EXIT WHEN AA_SOURCE_DATA.COUNT = 0;
FOR I IN 1 .. AA_SOURCE_DATA.COUNT LOOP
R_TARGET_DATA.OWNER := AA_SOURCE_DATA(I).OWNER;
R_TARGET_DATA.OBJECT_NAME := AA_SOURCE_DATA(I).OBJECT_NAME;
R_TARGET_DATA.COMM := ‘xxx’;
PIPE ROW(R_TARGET_DATA);
END LOOP;
END LOOP;
CLOSE P_SOURCE_DATA;
RETURN;
END;
PROCEDURE LOAD_TARGET_PARALLEL IS
BEGIN
EXECUTE IMMEDIATE ‘alter session enable parallel dml’;
INSERT /+parallel(t,4)/
INTO T_TARGET T
(OWNER, OBJECT_NAME, COMM)
SELECT OWNER, OBJECT_NAME, COMM
FROM TABLE(PIPE_TARGET_PARALLEL(CURSOR (SELECT /+parallel(s,4)/
*
FROM T_OBJECTS S)));
COMMIT;
END;
END;
以下实例代码为提小勇编写时间函数综合应用实例
时间维度表建表语句：
create table TIMEDIMENSION
(
date_key NUMBER not null,
full_date VARCHAR2(12),
day_of_week NUMBER,
day_number_in_month NUMBER,
day_number_overall NUMBER,
day_name VARCHAR2(9),
day_abbrev VARCHAR2(20),
weekday_flag VARCHAR2(1),
week_num_in_year NUMBER,
week_num_overall NUMBER,
week_begin_date VARCHAR2(12),
week_begin_date_key NUMBER,
month NUMBER,
month_number_overall NUMBER,
month_name VARCHAR2(9),
month_abbrev VARCHAR2(20),
quarter NUMBER,
year NUMBER,
yearmo NUMBER,
last_day_in_month_flag VARCHAR2(1),
same_weekday_year_ago_date DATE
)
tablespace TBS_ORACLE_MRMS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
以下代码为生成2005年1月1日到2025年数据，根据时间维度完成日、月、年、时分秒的拆分，还有具体其他逻辑，想了解这张时间维度表以及时间
日期查询SQL具体逻辑请联系小勇，或者麻烦小勇梳理这张表的逻辑需求。以下为50%情况下基本适用的日期查询
代码如下：
INSERT INTO TIMEDIMENSION NOLOGGING
–ALTER TABLE TIMEDIMENSION MODIFY WEEK_BEGIN_DATE VARCHAR2(12 BYTE);
–ALTER TABLE TIMEDIMENSION MODIFY FULL_DATE VARCHAR2(12 BYTE);
–ALTER TABLE TIMEDIMENSION MODIFY MONTH_ABBREV VARCHAR2(20)
/SELECT ‘alter table ‘ || TABLE_NAME || ‘ enable constraint ‘ || CONSTRAINT_NAME || ‘;’ FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘R’; SELECT ‘alter table ‘ || TABLE_NAME || ‘ disable constraint ‘ || CONSTRAINT_NAME || ‘;’ FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘R’;/
WITH ALL_TIMES AS
(SELECT /*+ MATERIALIZE */
TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, ‘YYYY’), -132) + ROWNUM – 1,
‘YYYYMMDD’)) “DATE_KEY”,
REPLACE(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, ‘YYYY’), -132) + ROWNUM – 1,
‘Dy/MON/YYYY’,
‘nls_date_language=american’),
‘2005’,
’05’) “FULL_DATE”,
ADD_MONTHS(TRUNC(SYSDATE, ‘YYYY’), -132) + ROWNUM – 1″DEMO_DATE”
FROM ALL_OBJECTS
WHERE ROWNUM <= ADD_MONTHS(LAST_DAY(TRUNC(SYSDATE, 'DD')), 109) -
ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -132))SELECT DATE_KEY,
FULL_DATE,
TO_NUMBER(TO_CHAR(DEMO_DATE, ‘D’)) “DAY_OF_WEEK”,
CAST(EXTRACT(DAY FROM DEMO_DATE) AS NUMBER) “DAY_NUMBER_IN_MONTH”,
ROWNUM”DAY_NUMBER_OVERALL”,
TO_CHAR(DEMO_DATE, ‘DAY’) “DAY_NAME”,
TO_CHAR(DEMO_DATE, ‘DY’) “DAY_ABBREV”,
CASE
WHEN TRIM(TO_CHAR(DEMO_DATE, ‘DAY’)) = ‘SATURDAY’ OR
TRIM(TO_CHAR(DEMO_DATE, ‘DAY’)) = ‘SUNDAY’ THEN
‘N’
ELSE
‘Y’
END AS “WEEKDAY_FLAG”,
CAST(TO_CHAR(DEMO_DATE, ‘IW’) AS NUMBER) “WEEK_NUMBER_IN_YEAR”,
CAST(TO_CHAR(DEMO_DATE, ‘WW’) AS NUMBER) “WEEK_NUMBER_OVERALL”,
TO_CHAR(TRUNC(DEMO_DATE, ‘DD’) – TO_CHAR(DEMO_DATE, ‘D’) + 1,
‘Dy/MON/YYYY’,
‘nls_date_language=american’) “WEEK_BEGIN_DATE”,
CAST(TO_CHAR(TRUNC(DEMO_DATE, ‘DD’) – TO_CHAR(DEMO_DATE, ‘D’) + 1,
‘YYYYMMDD’) AS NUMBER) “WEEK_BEGIN_DATE_KEY”,
EXTRACT(MONTH FROM DEMO_DATE) “MONTH”,
ROW_NUMBER() OVER(PARTITION BY SUBSTR(DATE_KEY, 1, 6) ORDER BY FULL_DATE) “MONTH_NUMBER_OVERALL”,
TO_CHAR(DEMO_DATE, ‘MONTH’, ‘nls_date_language=american’) “MONTH_NAME”,
TO_CHAR(DEMO_DATE, ‘MON’) “MONTH_ABBREV”,
TO_CHAR(DEMO_DATE, ‘Q’) “QUARTER”,
EXTRACT(YEAR FROM DEMO_DATE) “YEAR”,
EXTRACT(YEAR FROM DEMO_DATE) || EXTRACT(MONTH FROM DEMO_DATE) “YEARMO”,
CASE
WHEN DATE_KEY = MAX(DATE_KEY)
OVER(PARTITION BY SUBSTR(DATE_KEY, 1, 6)) THEN
‘Y’
ELSE
‘N’
END “LAST_DAY_IN_MONTH_FLAG”,
LAG(DEMO_DATE) OVER(PARTITION BY TRUNC(SYSDATE, ‘D’) – 7 ORDER BY TRUNC(SYSDATE, ‘D’) – 7) “SAME_WEEK_”
FROM ALL_TIMES
ORDER BY DEMO_DATE ASC