行列转换sql

CREATE OR REPLACE TYPE TYPE_IN IS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION FUN_TO_T_IN (P_IN VARCHAR2) RETURN T_IN AS
@ F*w)o1F&}?!AF$T Z0V_RETURN T_IN DEFAULT T_IN();ITPUB个人空间.o$F&O%uys)e/VR)K:Y(Q3d
V_IN VARCHAR2(32767);ITPUB个人空间SSI emY)y$OA3X0x
V_COUNT NUMBER DEFAULT 0;ITPUB个人空间9H$Q
w'V0k]X
BEGINITPUB个人空间)`AMYKAp
V_IN := REPLACE(P_IN || ',', CHR(10), '');
2^)`i(k,c6w H0WHILE(INSTR(V_IN, ',') > 0) LOOPITPUB个人空间Q\4k/j+f:@\
V_RETURN.EXTEND;
(T7Z hp A^ m3A0V_COUNT := V_COUNT + 1;ITPUB个人空间W[X,n4aj
V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, ',') - 1);ITPUB个人空间e|2Qp&Y6@x
V_IN := SUBSTR(V_IN, INSTR(V_IN, ',') + 1);ITPUB个人空间{;I+AZT VP i
END LOOP;ITPUB个人空间8] P8T mEz$s6K z]
RETURN V_RETURN;ITPUB个人空间 K Ei*eSQe
END;

测试如下:

SQL> select * from table(select fun_to_t_in('1,2,3,4') from dual) ;
~my~| R~(Ep0 
"W D kr
kA-P1w J+D0COLUMN_VALUEITPUB个人空间sdM1K!~Ct$s1t!w\
------------
sqC}U*l0           1
pH t)J!j1p[0           2
tf9_0iY6J"zV0           3
kQB;Z*DF"q0           4