先声明:转载的博客:
解读是自己做的
创建类
CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(500);/
创建函数
CREATE OR REPLACE FUNCTION SEND_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; /
测试:
select ci_id,wm_concat(stu_name) from(SELECT s.stu_name, c.ci_id FROM pm_stu s, pm_ci c WHERE stu_id in (SELECT * FROM TABLE(CAST(SEND_SPLIT(c.stu_ids, ',')AS mytable)))order by stu_id)group by ci_id/
结果:
CI_ID WM_CONCAT(STU_NAME)
--------------- ------------------------------------ 1 张三,李四,赵六,王五 2 张三,赵六函数解读: