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