oracle function을 이용한 join

1.object type 생성

CREATE OR REPLACE 
TYPE OBJ_TYPE1 AS OBJECT (
  c1 int,
  c2 int
)
;

2.collection type 생성

CREATE OR REPLACE 
TYPE OBJ_TBL_TYPE1 AS TABLE OF OBJ_TYPE1
;

3.function 생성 

CREATE OR REPLACE function func1

return obj_tbl_type1
pipelined
is
  v_obj obj_type1;
begin
  for obj in (SELECT 1 AS idx1 , 10 AS idx2 FROM dual UNION ALL
              SELECT 2 AS idx1 , 20 AS idx2 FROM dual
             )loop
    v_obj := obj_type1(obj.idx1, obj.idx2);
    pipe row(v_obj);
  end loop;
end;

4.테스트

select * from table(FUNC1())