行列轉換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
V_RETURN T_IN DEFAULT T_IN();
V_IN VARCHAR2(32767);
V_COUNT NUMBER DEFAULT 0;
BEGIN
V_IN := REPLACE(P_IN || ',', CHR(10), '');
WHILE(INSTR(V_IN, ',') > 0) LOOP
V_RETURN.EXTEND;
V_COUNT := V_COUNT + 1;
V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, ',') - 1);
V_IN := SUBSTR(V_IN, INSTR(V_IN, ',') + 1);
END LOOP;
RETURN V_RETURN;
END;

測試如下:

SQL> select * from table(select fun_to_t_in('1,2,3,4') from dual) ;
 
COLUMN_VALUE
------------
           1
           2
           3
           4