17,086
社区成员
发帖
与我相关
我的任务
分享
--建立对象类型
CREATE or replace TYPE mytype AS OBJECT (
field1 NUMBER,
field2 VARCHAR2 (50)
);
--建立复合类型
CREATE TYPE mytypelist AS TABLE OF mytype;
--建立函数
CREATE OR REPLACE FUNCTION pipelineme2(v_cur sys_refcursor)
RETURN mytypelist PIPELINED
IS
SQL> with tmp as
2 (
3 select '1|a,b,c' str from dual union all
4 select '2|d,e,f' str from dual
5 )
6 select distinct id, regexp_substr(name,'[^,]+',1,level) name
7 from (select substr(str,1,instr(str,'|')-1) id,
8 substr(str,instr(str,'|')+1) name
9 from tmp
10 )
11 connect by level<=length(name)-length(replace(name,',',''))+1
12 order by id, name;
ID NAME
-------------- --------------
1 a
1 b
1 c
2 d
2 e
2 f
6 rows selected