How does a function return a result set in Oracle?

How does a function return a result set in Oracle?

In Oracle, function return result sets are sometimes used. The following is a demo:

create or replace type t_test as object(id integer,create_time date,object_name varchar2(60));create or replace type t_test_table as table of t_test;

1. Using Arrays

Create or replace function f_test (n in number default null) return t_test_table asv_test t_test_table: = t_test_table (); beginfor I in 1 .. n loopv_test.extend (); v_test (v_test.count): = t_test (I, sysdate, 'name' | I); end loop; return v_test; end f_test; /SQL> select * from table (f_test (5 )); ID CREATE_TIME OBJECT_NAME -------- ------------ ------------- 1 07-4 month-15 name12 07-4 month-15 name23 07-4 month-15 name34 07-4 month-15 name45 07-4 month-15 name5

2. Pipeline Functions
 

Create or replace function f_test_pipe (n in number default null) return t_test_tablePIPELINED asv_test t_test_table: = t_test_table (); beginfor I in 1 .. nvl (n, 100) looppipe row (t_test (I, sysdate, 'name' | I); end loop; return; end f_test_pipe; /SQL> select * from table (f_test_pipe (5 )); ID CREATE_TIME OBJECT_NAME ---------- ------------ ---------------- 1 07-4 month-15 mc12 07-4 month-15 mc23 07-4 month-15 mc34 07-4 month-15 mc45 07-4 month-15 mc5