oracle 根据CHR(10)换行符逐行提取拆分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 )
    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 ) )

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

本文技术来源国外