oracle 根据CHR(10)换行符逐行提取拆分CLOB为多行数据

项目中遇到了难题，使用润乾报表打印长篇幅文章，单页数据超长，无法正常打印。于是我们想出了，将oracle CLOB类型拆分为一行一行的段落，返给报表，做成动态数据，实现多页可以打印出来。我已经验证过了，拆分输出几十个段落的文章只需要很短的时间。

以下方法使用的一种有趣的优化技术，将CLOB拆分成32K的varchar2块。
可以使用DBMS_LOB包或通过重载的SQL函数直接拆分CLOB；但是CLOBS成本太高。另一方面，Varchar2变量体积小，使得它们内部的子解析速度要快得多。但是，这样做的话需要更加谨慎，以确保这些小块不会意外地将一行分割成两行身子N多行。

另外，我也假设没有一行超过32K，对于这种情况这个函数更具优势，因为输出是一个SQL集合，其varchar2限制为4000字节。
返回的VCARRAY类型是一个简单的表集合类型。

首先需要创建一个表集合类型

CREATE OR REPLACE TYPE VCARRAY as table of varchar2(4000)

下面创建一个函数：

CREATE OR REPLACE FUNCTION split_clob(p_clob IN CLOB, p_delimiter IN VARCHAR2 DEFAULT CHR(10))
    RETURN vcarray
    PIPELINED
IS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    c_chunk_limit   CONSTANT INTEGER := 32767;
    v_clob_length            INTEGER := DBMS_LOB.getlength(p_clob);
    v_clob_index             INTEGER;
    v_chunk                  VARCHAR2(32767);
    v_chunk_end              INTEGER;
    v_chunk_length           INTEGER;
    v_chunk_index            INTEGER;
    v_delim_len              INTEGER := LENGTH(p_delimiter);
    v_line_end               INTEGER;
BEGIN
    v_clob_length := DBMS_LOB.getlength(p_clob);
    v_clob_index := 1;

    WHILE v_clob_index <= v_clob_length
    LOOP
        /*
            Pull one 32K chunk off the clob at a time.
            This is because it's MUCH faster to use built in functions
            on a varchar2 type than to use dbms_lob functions on a clob.
        */
        v_chunk := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);

        IF v_clob_index > v_clob_length - c_chunk_limit
        THEN
            -- if we walked off the end the clob,
            -- then the chunk is whatever we picked up at the end
            -- delimited or not
            v_clob_index := v_clob_length + 1;
        ELSE
            v_chunk_end := INSTR(v_chunk, p_delimiter, -1);

            IF v_chunk_end = 0
            THEN
                DBMS_OUTPUT.put_line('No delimiters found!');
                RETURN;
            END IF;

            v_chunk := SUBSTR(v_chunk, 1, v_chunk_end);
            v_clob_index := v_clob_index + v_chunk_end + v_delim_len - 1;
        END IF;

        /*
            Given a varchar2 chunk split it into lines
        */

        v_chunk_index := 1;
        v_chunk_length := NVL(LENGTH(v_chunk), 0);

        WHILE v_chunk_index <= v_chunk_length
        LOOP
            v_line_end := INSTR(v_chunk, p_delimiter, v_chunk_index);

            IF v_line_end = 0 OR (v_line_end - v_chunk_index) > 4000
            THEN
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, 4000));
                v_chunk_index := v_chunk_index + 4000;
            ELSE
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, v_line_end - v_chunk_index));
                v_chunk_index := v_line_end + v_delim_len;
            END IF;
        END LOOP;
    END LOOP;

    RETURN;
EXCEPTION
    WHEN no_data_needed
    THEN
        NULL;
END split_clob;

一切就这么简单。使用方法：

split_clob(p_clob,p_delimiter )

第一个参数 CLOB数据，第二个参数可不传，默认换行符，可换成其他分隔符

但是我们遇到个问题，split_clob(p_clob,p_delimiter )等方法返回的是一个数据集合，如何变成表字段查出来呢？

其实很简单：

select  * FROM table( split_clob(p_clob,p_delimiter ) )

将数据集合换成表类型查出来就行了。

本文技术来源国外SEAN D. STUBER文章，本站进行转载记录，以备后续查阅，也方便其他同学使用！