In Oracle, how does a function return a result set?
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
V_test t_test_table: = t_test_table ();
Begin
For I in 1. n loop
V_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 month-15 name1
2 07-4 months-15 name2
3 07-4-15 name3
4 months-15 name4
5 07-4-15 name5
2. Pipeline Functions
Create or replace function f_test_pipe (n in number default null)
Return t_test_table
PIPELINED
V_test t_test_table: = t_test_table ();
Begin
For I in 1 .. nvl (n, 100) loop
Pipe 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 month-15 mc1
2 07-4-15 mc2
3 month-15 mc3
4 month-15 mc4
5 month-15 mc5