Oracle存储过程返回数组的方法

oracle 存储过程返回数组的方法

建立包

createorreplacepackagetestis

TYPEfilename_arrayISTABLEOFvarchar();

filenamefilename_array;

endtest;

建立存储过程

createorreplaceproceduretest_array(v_cfjgouttestfilename_array)is

beginDECLAREinumber;

D_cfjgdic_cfjg%rowTYPE;

D_nrdic_cfjg%rowTYPE;

cursorcisSELECT*FROMdic_cfjg;

BEGIN

i:=;

v_cfjg:=testfilename_array();数组初始化

openc;

LOOPfetchcintoD_cfjg;

EXITWHENc%NOTFOUND;

i:=i+;

v_cfjgEXTEND;

DBMS_OUTPUTPUT_LINE(TO_CHAR(D_cfjgdm));

v_cfjg(unt):=D_cfjgdm;

DBMS_OUTPUTPUT_LINE(v_cfjg(unt));

测试

FETCHCINTOD_cfjg;

EXITWHENc%NOTFOUND;

ENDLOOP;

end;

EXCEPTION

WHENTOO_MANY_ROWSTHEN

DBMS_OUTPUTPUT_LINE(TOO_MANY_ROWS);

WHENOTHERSTHENDBMS_OUTPUTPUT_LINE(sqlerrm);

endtest_array;