oracle字符串转表,Oracle分割字符串转成表的方法

--分割字符串转成表

(1)定义嵌套表类型

CREATE OR REPLACE TYPE "MYTABLE"                                                                          AS TABLE OF varchar2(100)

(2)创建函数

CREATE OR REPLACE FUNCTION split

(src VARCHAR2, delimiter varchar2)

RETURN mytable IS

psrc VARCHAR2(500);

a mytable := mytable();

i NUMBER := 1; --

j NUMBER := 1;

BEGIN

psrc := RTrim(LTrim(src, delimiter), delimiter);

LOOP

i := InStr(psrc, delimiter, j);

--Dbms_Output.put_line(i);

IF i>0 THEN

a.extend;

a(a.Count) := Trim(SubStr(psrc, j, i-j));

j := i+1;

--Dbms_Output.put_line(a(a.Count-1));

END IF;

EXIT WHEN i=0;

END LOOP;

IF j < Length(psrc) THEN

a.extend;

a(a.Count) := Trim(SubStr(psrc, j, Length(psrc)+1-j));

END IF;

RETURN a;

END;

(3)调用方法

SELECT column_value as login_name

FROM TABLE(CAST(split('苹果，香蕉，葡萄，荔枝，芒果', '，') AS MYTABLE));