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
All for u
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步