在Oracle中，是否可以将用逗号分隔的非常大的字符串(clob)转换为性能更好的表

I need to convert very large clob string in to table by comma delimiter.

我需要用逗号分隔符将非常大的clob字符串转换为table。

Below function takes very long time. is there any fast function which returns table.

下面的功能需要很长时间。是否有快速返回表的函数。

create or replace 
FUNCTION        UDF_STRSPLIT2 (
   P_STR     IN CLOB,
   P_DELIM   IN VARCHAR2 DEFAULT ';' ,
   P_LIKE    IN INT DEFAULT 0
)
   RETURN MYTABLETYPE
AS
   L_STR    CLOB DEFAULT P_STR || P_DELIM ;
   L_N      NUMBER;
   L_DATA   MYTABLETYPE := MYTABLETYPE ();
BEGIN
   LOOP
      L_N := INSTR (L_STR, P_DELIM);
      EXIT WHEN (NVL (L_N, 0) = 0);
      L_DATA.EXTEND;
      L_DATA (L_DATA.COUNT) :=
         LTRIM (RTRIM (SUBSTR (L_STR, 1, L_N - 1)))
         || CASE WHEN P_LIKE = 0 THEN '' ELSE '%' END;
      L_STR := SUBSTR (L_STR, L_N + LENGTH (P_DELIM));
   END LOOP;

   RETURN L_DATA;
END;

3 个解决方案

#1

You can speed this one up by an order of magnitude with DBMS_LOB instead of regular instr/substr:

您可以使用DBMS_LOB而不是常规的instr/substr将这个速度提高一个数量级:

CREATE OR REPLACE FUNCTION DROPME$STRSPLIT2 (
   P_STR     IN CLOB,
   P_DELIM   IN VARCHAR2 DEFAULT ';' ,
   P_LIKE    IN INT DEFAULT 0
)
   RETURN SYS.ODCIVARCHAR2LIST
AS
  L_DATA SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST ();
  L_STR  CLOB := P_STR || P_DELIM;  
  L_SUBSTR VARCHAR2(4000);
  L_STEP PLS_INTEGER := 0;
  L_THIS INT := 1;
  L_PREV INT := 0;
  L_END CHAR := CASE P_LIKE WHEN 0 THEN NULL ELSE '%' END;
BEGIN  
  LOOP
    L_STEP := L_STEP + 1;
    L_THIS := DBMS_LOB.INSTR(L_STR, P_DELIM, L_PREV + 1, 1);    
    EXIT WHEN L_THIS = 0;
    L_SUBSTR := 
    TRIM(
      DBMS_LOB.SUBSTR(
        L_STR, 
        L_THIS - L_PREV - 1,
        L_PREV + 1
      )
    );        
    L_PREV := L_THIS;    
    L_DATA.EXTEND();
    L_DATA(L_STEP) := L_SUBSTR || L_END;
  END LOOP;
  RETURN L_DATA;
END;

Test case, process 60kb CLOB 10 times:

测试用例，处理60kb CLOB 10次:

Your function:

你的函数:

18:15:50 SQL> l
  1  DECLARE
  2    VAL CLOB;
  3    RESULT SYS.ODCIVARCHAR2LIST;
  4  BEGIN
  5    SELECT C INTO VAL FROM DROPME$C;
  6    FOR I IN 1 .. 10 LOOP
  7      RESULT := DROPME$STRSPLIT1(VAL);
  8    END LOOP;
  9* END;
18:15:54 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.56

Upgraded function:

升级功能:

18:17:12 SQL> l
  1  DECLARE
  2    VAL CLOB;
  3    RESULT SYS.ODCIVARCHAR2LIST;
  4  BEGIN
  5    SELECT C INTO VAL FROM DROPME$C;
  6    FOR I IN 1 .. 10 LOOP
  7      RESULT := DROPME$STRSPLIT2(VAL);
  8    END LOOP;
  9* END;
18:17:14 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.87

Upd. to make sure they provide the same output:

乌利希期刊指南。确保它们提供相同的输出:

18:20:08 SQL> l
  1  SELECT * FROM TABLE(
  2    DROPME$STRSPLIT1('a;b;c;d;f')
  3* )
18:20:10 SQL> /

COLUMN_VALUE
------------
a
b
c
d
f

and upgraded

和升级

18:20:16 SQL> l
  1  SELECT * FROM TABLE(
  2    DROPME$STRSPLIT2('a;b;c;d;f')
  3* )
18:20:20 SQL> /

COLUMN_VALUE
------------
a
b
c
d
f

#2

If i understand well, then you can do this(in this case i used comma delimiter):

如果我理解得很好，那么您可以这样做(在本例中我使用了逗号分隔符):

  select to_char(regexp_substr (cad , '[^,]+', 1, rownum))
  from clob_table
  connect by level <= regexp_count(cad,',');

In this example clob_table is a table that contains one row '1,2,3,4,5,6,7'. Then if you want this as a table you can create table as select, using above query.

在本例中，clob_table是一个包含一行“1,2,3,4,5,6,7”的表。然后，如果您想要它作为一个表，您可以使用上面的查询创建表作为select。

#3

You can use the simple DBMS_LOB to manipulate CLOB

您可以使用简单的DBMS_LOB来操作CLOB。

create or replace FUNCTION UDF_STRSPLIT2(P_STR   IN CLOB,
                                           P_DELIM IN VARCHAR2 DEFAULT ';',
                                           P_LIKE  IN INT DEFAULT 0)
    RETURN MYTABLETYPE AS
    L_STR  CLOB DEFAULT P_STR||P_DELIM;
    L_N    NUMBER;
    L_DATA MYTABLETYPE := MYTABLETYPE();  
    L_ST NUMBER:=1;
    L_ED NUMBER:=1;

  BEGIN
     IF LENGTH(L_STR)=1 --IF clob is empty
     THEN 
     RETURN L_DATA;
     END IF;
    LOOP
      L_DATA.EXTEND;  
      L_ED := (DBMS_LOB.INSTR(L_STR, P_DELIM, 1, L_DATA.COUNT)) - 1; --End postion
      L_DATA(L_DATA.COUNT) := DBMS_LOB.SUBSTR(L_STR,
                                              (L_ED - L_ST) + 1,
                                              L_ST);
      L_ST := (DBMS_LOB.INSTR(L_STR, P_DELIM, 1, L_DATA.COUNT)) + 1; --Start position
      EXIT WHEN L_DATA(L_DATA.COUNT) IS NULL;
    END LOOP;
    L_DATA.TRIM;
    RETURN L_DATA;
  END;