本文介绍了在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
这篇关于在Oracle中,可以将以逗号分隔的非常大的字符串(clob)转换为具有更好性能的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!