In Oracle, it is sometimes used to return a result set with a function, and here is the 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 an array of methods
Create or Replace function F_test (n in number default null)
Return t_test_table as
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 July-April -15 name1
2 July-April -15 name2
3 July-April -15 Name3
4 July-April -15 name4
5 July-April -15 name5
2. Using the pipe function
Create or Replace function F_test_pipe (n in number default null)
Return t_test_table
Pipelined as
V_test t_test_table: = T_test_table ();
Begin
For I in 1.. NVL (n, +) 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 July-April -15 MC1
2 July-April -15 MC2
3 July-April -15 MC3
4 July-April -15 MC4
5 July-April -15 MC5
How a function returns a result set in Oracle