Oracle Stored Procedure returned array method

Method for returning an array from an oracle stored procedure:

1. Create a package

Create or replace package test is

TYPE filename_array is table of varchar2 (1 );

Filename filename_array;

End test;

2. Create a stored procedure

Create or replace procedure test_array (v_cfjg out test. filename_array) is

Begin DECLARE I number;

D_cfjg dic_cfjg % rowTYPE;

D_nr dic_cfjg % rowTYPE;

Cursor c1 is SELECT * FROM dic_cfjg;

BEGIN

I: = 0;

V_cfjg: = test. filename_array (); -- array Initialization

Open c1;

LOOP fetch c1 into D_cfjg;

Exit when c1 % NOTFOUND;

I: = I + 1;

V_cfjg.EXTEND;

DBMS_OUTPUT.PUT_LINE (TO_CHAR (D_cfjg.dm ));

V_cfjg (v_cfjg.count): = D_cfjg.dm;

DBMS_OUTPUT.PUT_LINE (v_cfjg (v_cfjg.count ));

Test

FETCH C1 INTO D_cfjg;

Exit when c1 % NOTFOUND;

End loop;

End;

EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ('too _ MANY_ROWS ');

When others then DBMS_OUTPUT.PUT_LINE (sqlerrm );

The methods for returning arrays from the oracle stored procedure will introduce the two methods mentioned above. If you have a better solution, share it with everyone. Don't swallow it by yourself. Finally, I hope the content above will help you.