Returning a 'table' from a PL/SQL function

oracle function 기능중 table로 결과값을 리턴받고 싶을때를 위해 찾아봤더니.. 너무나도 깔끔하고 아름다운 글이 있었다.. ^^

With collections, it is possible to return a table from a pl/sql function.

First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object (
  i number,
  n varchar2(30)
);
/

Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;
/

Now, we're ready to actually create the function:

create or replace function return_table return t_nested_table as
  v_ret   t_nested_table;
begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;
/

Here's how the function is used:

select * from table(return_table);

     1 one
     2 two
     3 three