Function怎么返回一个数据集?

王朝other·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航