oracle clob 分割,Oracle clob字符串分割函数

标签：

/******************************************************

ORACLE clob字符串分割函数

首先创建以下type

create or replace type type_str is table of varchar2(1000);

*******************************************************/

FUNCTION FUNC_SPLIT_CLOB(P_STR IN CLOB, P_DELIMITER IN VARCHAR2 DEFAULT ','/*, LEN IN OUT NUMBER*/) RETURN TYPE_STR

AS

RS TYPE_STR := TYPE_STR();

L_STR CLOB := P_STR;

L_DELIMITER CLOB:= TO_CLOB(P_DELIMITER);

L_LEN NUMBER := 0;

N NUMBER;

V_STR VARCHAR2(32767);

LEN NUMBER;

BEGIN

L_LEN := LENGTH(P_DELIMITER);

DBMS_LOB.APPEND(DEST_LOB => L_STR,SRC_LOB =>L_DELIMITER);

LOOP

N := DBMS_LOB.INSTR(LOB_LOC => L_STR,PATTERN => L_DELIMITER);

V_STR:= TRIM(DBMS_LOB.SUBSTR(LOB_LOC => L_STR,AMOUNT => N-1,OFFSET => 1));

EXIT WHEN V_STR IS NULL;

RS.EXTEND;

RS(RS.COUNT) := V_STR ;

LEN := LENGTH(RS(RS.COUNT))+DBMS_LOB.GETLENGTH(L_DELIMITER)+LEN;

DBMS_LOB.ERASE(LOB_LOC => L_STR,AMOUNT => LEN);

END LOOP;

RETURN RS;

END FUNC_SPLIT_CLOB;

SQL> declare

2 lens number default 0;

3 RS TYPE_STR := TYPE_STR();

4 l_string clob:=to_clob('A,AA,AAA,AAAAAB,BB,BBB,BBBB');

5 begin

6 rs:= func_split_clob(p_string => l_string,len => lens);

7 for i in rs.first .. rs.last loop

8 dbms_output.put_line(rs(i));

9 end loop;

10 end;

11 /

A

AA

AAA

AAAAAB

BB

BBB

BBBB

标签：

来源： https://www.cnblogs.com/ayumie/p/9792662.html