更新:请参阅第一个注释以获取不带大小限制的TABLE解决方案.
返回VARRAY或使用PIPELINED函数从中查询.
>对于VARRAY,请参见this文章以获取详细信息.从那里的代码示例:
CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
/
CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
l_data EmpArray := EmpArray();
CURSOR c_emp IS SELECT ename FROM EMP;
BEGIN
FOR emp_rec IN c_emp LOOP
l_data.extend;
l_data(l_data.count) := emp_rec.ename;
END LOOP;
RETURN l_data;
结束;
>对于PiPELINED功能,请检出here.代码示例:
create or replace function Lookups_Fn return lookups_tab
pipelined
is
v_row lookup_row;
begin
for j in 1..10
loop
v_row :=
case j
when 1 then lookup_row ( 1, 'one' )
--...
when 7 then lookup_row ( 7, 'seven' )
else lookup_row ( j, 'other' )
end;
pipe row ( v_row );
end loop;
return;
end Lookups_Fn;
/
select * from table ( Lookups_Fn );