Dbms_Uitility包+table函数

create table t(
id number,
name varchar2(100)
);

insert into t values(1,'a,b,c');
insert into t values(2,'e,f');
commit;

CREATE OR REPLACE TYPE TP_T AS
OBJECT(
ID NUMBER,
NAME VARCHAR2(100));
DROP TYPE TP_TAB_T;
CREATE OR REPLACE TYPE TP_TAB_T AS TABLE OF TP_T;
CREATE OR REPLACE FUNCTION FUNC_ETL_T RETURN TP_TAB_T
AS
V_NAME DBMS_UTILITY.LNAME_ARRAY;
V_IDX NUMBER;
V_TAB_T TP_TAB_T := TP_TAB_T();
BEGIN
FOR I IN (SELECT ID,NAME FROM T) LOOP
DBMS_UTILITY.COMMA_TO_TABLE(I.NAME,V_IDX,V_NAME);
FOR J IN 1..V_IDX LOOP
V_TAB_T.EXTEND;
V_TAB_T(V_TAB_T.COUNT):=TP_T(I.ID,V_NAME(J));
END LOOP;
END LOOP;
RETURN V_TAB_T;
END FUNC_ETL_T;

select * from t;
select * from table(func_etl_t);