Oracleclob字符串分割函数
/******************************************************
ORACLE clob字符串分割函数
⾸先创建以下type
create or replace type type_str is table of varchar2(1000);
*******************************************************/
oracle 字符串转数组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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。