oracle基于CHR(10)换行符，将CLOB逐行展开并分割成多行数据

我在项目中遇到了错误。我用用完报告打印了一篇长文章。单页数据过长，无法正常打印。因此我们想到了将 Oracle CLOB 类型分解为逐行段落并将它们返回到报表中以创建动态数据，以便可以打印多页的想法。我确保只需要很短的时间就可以将一篇由几十个段落组成的文章分割并发表。

以下方法使用一种有趣的优化技术将 CLOB 拆分为 32K varchar2 块。
CLOB可以直接使用DBMS_LOB包或者重载的SQL函数进行拆分；但CLOBS太贵了。另一方面，Varchar2 变量的小尺寸使得子解析速度更快。但是，执行此操作时必须更加小心，以免这些小块意外地将行拆分为两行和多行。

我还假设没有任何行超过 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 ) )

只需将数据集改为表格类型，然后查找即可。