Three ways to return an Oracle object table using a function _oracle

Because you want to return a Table object, you declare the global type first, regardless of what method is followed, and the field variable type is object and cannot be a record:

Create or replace type T_test as Object (
 EMPNO number  (4),
 ename  VARCHAR2,
 JOB   VARCHAR2 (9 ),
 SAL number   (7,2)
);

Create or replace type t_test_table as table of t_test;

As for the method of returning the Table object, three kinds are found:
1, with the array

Create or Replace function F_test_array (v_deptno in number default null) return
t_test_table
is 
v_test t_ Test_table: = T_test_table ();
Cursor cur is select empno, ename, Job, Sal from emp where deptno = V_deptno;
Begin for
C in cur loop
v_test.extend ();
V_test (v_test.count): = T_test (C.empno, C.ename, C.job, c.sal);
End Loop;
return v_test;
End

2, with pipe

Create or Replace function F_test_pipe (v_deptno in number default null) return
t_test_table pipelined 
is
v_ Test t_test_table: = T_test_table ();
Cursor cur is select empno, ename, Job, Sal from emp where deptno = V_deptno;
Begin for 
C in cur loop
pipe row (t_test (C.empno, C.ename, C.job, c.sal)); 
End Loop; 
return; 
End

Both of these need to be traversed by a cursor to get the Table object, the performance of the estimated 2nd higher.

3, with collect (do not need cursors, the code is relatively simple)

Create or Replace function F_test_collect (v_deptno in number default null) return
t_test_table
is 
v_test T_test_table: = T_test_table ();
Begin
Select T_test (empno, ename, Job, sal) bulk collect into V_test from emp where deptno = V_deptno;
return v_test;
End

It should be noted that the output is instantiated before select INTO:
t_test (empno, ename, Job, Sal)
Otherwise there will be an error:
ORA-00947: Not enough values (object multiple fields)
ORA-00932: inconsistent data type (object single field)
And if the type is declare directly in the Plsql block, it is not necessary to first object the output result.
Three function definitions can test the output the same way:

SELECT * FROM table (f_test_pipe);
SELECT * FROM table (F_test_array);
SELECT * FROM table (F_test_collect (30));

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.