频道导航

Oracle clob字符串分割函数

2020-08-03 Oracle 前端之家

前端之家收集整理的这篇文章主要介绍了Oracle clob字符串分割函数，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

@H_404_0@

/*@H_502_9@*****************************************************
     ORACLE clob字符串分割函数 

@H_502_9@     首先创建以下type

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

  ******************************************************@H_502_9@*/@H_502_9@
  FUNCTION FUNC_SPLIT_CLOB(P_STR IN CLOB,P_DELIMITER IN VARCHAR2 DEFAULT @H_502_9@‘@H_502_9@,@H_502_9@‘@H_502_9@/*@H_502_9@,LEN IN OUT NUMBER@H_502_9@*/@H_502_9@) RETURN TYPE_STR
  AS
    RS TYPE_STR :@H_502_9@= TYPE_STR();
    L_STR CLOB :@H_502_9@= P_STR;
    L_DELIMITER CLOB:@H_502_9@= TO_CLOB(P_DELIMITER);
    L_LEN NUMBER :@H_502_9@= 0@H_502_9@;
    N     NUMBER;
    V_STR VARCHAR2(@H_502_9@32767@H_502_9@);
    LEN NUMBER;

  BEGIN 
    
     L_LEN :@H_502_9@= LENGTH(P_DELIMITER);
  
     DBMS_LOB.APPEND(DEST_LOB @H_502_9@=> L_STR,SRC_LOB =>L_DELIMITER);
     
     LOOP
          N :@H_502_9@= DBMS_LOB.INSTR(LOB_LOC => L_STR,PATTERN => L_DELIMITER);
          V_STR:@H_502_9@= TRIM(DBMS_LOB.SUBSTR(LOB_LOC => L_STR,AMOUNT => N-1@H_502_9@,OFFSET => 1@H_502_9@));
          
      EXIT WHEN V_STR IS NULL;
          RS.EXTEND;
          RS(RS.COUNT) :@H_502_9@=  V_STR ;
          LEN :@H_502_9@= LENGTH(RS(RS.COUNT))+DBMS_LOB.GETLENGTH(L_DELIMITER)+LEN;
          DBMS_LOB.ERASE(LOB_LOC @H_502_9@=> L_STR,AMOUNT => LEN);
     END LOOP;
     RETURN RS;

  END FUNC_SPLIT_CLOB;

@H_502_9@

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 /

AAAAAAAAAAABBBBBBBBBB

@H_404_0@