在Oracle中，是否可以将逗号分隔的非常大的字符串(clob)转换为具有更好性能的表
我需要通过逗号分隔符将非常大的 clob 字符串转换为表格.
I need to convert very large clob string in to table by comma delimiter.
以下功能需要很长时间.有没有快速返回表的函数.
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;
推荐答案
您可以使用 DBMS_LOB 而不是常规的 instr/substr 将这个速度提高一个数量级:
You can speed this one up by an order of magnitude with DBMS_LOB instead of regular 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 次:
Test case, process 60kb CLOB 10 times:
您的职能:
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
更新.以确保它们提供相同的输出:
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
并升级
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
相关文章