oracle clob分隔符,oracle根据特定分隔符单条数据分割为多行

先贴代码

create or replace type t_table1 is table of VARCHAR2(200)

CREATE OR REPLACE FUNCTION F_SPLIT(P_LIST CLOB, P_SEP VARCHAR2 := ',')

RETURN t_table1

PIPELINED

-- ============================================================

-- 返回字符串被指定字符分割后的表类型

-- ============================================================

IS

L_IDX PLS_INTEGER;

V_LIST VARCHAR2(4000) := P_LIST;

BEGIN

LOOP

L_IDX := INSTR(V_LIST, P_SEP);

IF L_IDX > 0 THEN

PIPE ROW(SUBSTR(V_LIST, 1, L_IDX - 1));

V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));

ELSE

IF V_LIST <> '' OR V_LIST IS NOT NULL THEN

PIPE ROW(V_LIST);

END IF;

EXIT;

END IF;

END LOOP;

END;

测试

1.默认的分隔符为“，”

SELECT * FROM TABLE(F_SPLIT('aaaa,bbbb,ccc,ddd,12,ds,cd'));

2.以@为分隔符

SELECT * FROM TABLE(F_SPLIT('aaaa,bbbb,ccc,ddd,12,ds,cd','@'));

3.以@为分隔符

SELECT * FROM TABLE(F_SPLIT('aaaa@bbbb@ccc@ddd@12@ds@cd','@'));