定义一个function，其返回的是table

1.
CREATE OR REPLACE PACKAGE ROME AS
AS
TYPE RefCursor IS REF CURSOR;
Function GetCompany(key IN char) return RefCursor;
END;
/
CREATE OR REPLACE PACKAGE BODY ROME IS
IS
Function GetCompany(key IN char) return RefCursor
Is
v_temp RefCursor;
BEGIN
OPEN v_temp FOR
SELECT * FROM Company WHERE com_ID =key;
return v_temp;
END GetCompany;
END;

2.(适用于PLSQL类型)
Type shifts_ty is RECORD(
     comp_code varchar2(10),
     SHIFT_CODE varchar2(10),
     sft_flg varchar2(10),
     beg_tm number,
     end_tm number,
     skills varchar2(10)) ;
Type shifts is Table of shifts_ty index by binary_integer;

FUNCTION test_proc(test varchar2)

return shifts is
shiftspkg SHIFTS; --表变量shiftspkg

cursor q1 is select
shifts.comp_code,shifts.shift_code,shifts.WRK_BEG_TM,shifts.WRK_end_TM,
shifts.skills from str_shifts shifts where comp_code ='TSI'; --str_shifts是与表变量shiftspkg结构完全相同的真实表
qty q1%rowtype;
begin

     open q1;
     loop
         fetch q1 into qty;
         exit when q1%notfound;

         for iCount in 1.. qty.skills.count
         loop
             shiftspkg(icount).comp_code:= qty.comp_code;
             shiftspkg(icount).SHIFT_CODE:= qty.shift_code;
             shiftspkg(icount).sft_flg:= 'SLOTS';
             shiftspkg(icount).beg_tm:= qty.wrk_beg_tm;
             shiftspkg(icount).end_tm:= qty.wrk_end_tm;
             shiftspkg(icount).skills:= qty.skills(icount);
         end loop;
     end loop;
     return shiftspkg;
end;
end;

3.使用于SQL类型
create or replace type myScalarType as object
    ( comp_code varchar2(10),
      shift_code varchar2(10),
      sft_flg varchar2(10),
      beg_tm number,
      end_tm number,
      skills varchar2(10)
       )

create or replace type myArrayType as table of myScalarType


FUNCTION test_proc(test varchar2) return myArrayType
     is
         l_data  myArrayType := myArrayType() ;
     begin
        for i in 1 .. 5
        loop
            l_data.extend;
            l_data( l_data.count ) := myScalarType( 'cc-'||i,
                                                    'sc-'||i,
                                                    'flg-'||i,
                                                    i,
                                                    i,
                                                    test||i );
        end loop;

        return l_data;
    end;

    end;

select *
       from THE ( select cast( pkg_test.test_proc('hello') as myArrayType )
                    from dual ) a
或
select *
       from table ( cast( my_function() as mytabletype ) )
      order by seq

http://www.itpub.net/thread-401951-1-1.html