create or replace type C1  is object(id int,name varchar2(10));

create or replace type C2  is table of C1;


create or replace function test(y int)
return C2
as
V_C2 C2:=C2();
 begin
   V_C2.extend;
   V_C2(V_C2.count):= C1(1,'a');
   return V_C2;
 end;
 
 
 select * from table(test(1));

借助管道表实现
create table test_res (id int, name varchar);
INSERT INTO test_res values(1,‘a’),(2,‘b’);

DROP TYPE IF EXISTS type_test_res CASCADE;
DROP TYPE IF EXISTS test_res_list CASCADE;
CREATE TYPE type_test_res AS object(id test_res.id%TYPE, name test_res.name%type);
CREATE TYPE test_res_list AS TABLE OF type_test_res;

CREATE OR REPLACE FUNCTION func_test_res()
RETURN test_res_list PIPELINED
IS
BEGIN
FOR rec IN (select * FROM test_res) – 此处必须要有括号
– 或者 FOR rec IN (select id,name FROM test_res) – 此处必须要有括号
loop
PIPE ROW (type_test_res(rec.id, rec.name));
END loop;
EXCEPTION
WHEN OTHERS THEN NULL;
END;

SELECT * FROM TABLE(func_test_res);