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

我需要通过逗号分隔符将非常大的 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 ();
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;

您可以使用 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;

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

你的职能：

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

升级功能：

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

更新。确保它们提供相同的输出：

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

并升级了

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