Using bulk collect to realize cursor batch FET under Oracle

Copy code code as follows:

Declare

Cursor C1 is select * from T_depart;

V_depart T_depart%rowtype;

Type V_code_type is table of T_depart.depart_code%type;

V_code V_code_type;

Type V_name_type is table of T_depart.depart_name%type;

V_name V_name_type;

Begin

Open C1;

Fetch C1 Bulk collect into V_code, v_name;

For I in 1..v_code.count loop

Dbms_output.put_line (V_code (i) | | ' '|| V_name (i));

End Loop;

Close C1;

End

Through the above, you can find that if a lot of columns, to define a set for each column seems to be a bit cumbersome, you can combine the collection and%rowtype together to use simplified procedures!

Copy code code as follows:

Declare

Cursor C1 is select * from T_depart;

Type V_depart_type is table of T_depart%rowtype;

V_depart V_depart_type;

Begin

Open C1;

Fetch C1 bulk collect into V_depart;

For I in 1..v_depart.count loop

Dbms_output.put_line (V_depart (i) depart_code| | ' '||

V_depart (i). Depart_name);

End Loop;

Close C1;

End

You can use the Count property of the collection when you output the results, and you can use the%rowtype type's content when referencing the contents of the V_depart (i). Depart_code, not V_depart.depart_ Code (i), of course, has no such wording, even if the meaning is not the same.

Copy code code as follows:

Declare

Cursor C1 is select * from T_depart;

Type V_depart_type is table of T_depart%rowtype;

V_depart V_depart_type;

Begin

Open C1;

Fetch C1 bulk collect into V_depart;

For I in V_depart.first. V_depart.last Loop

Dbms_output.put_line (V_depart (i) depart_code| | ' '||

V_depart (i). Depart_name);

End Loop;

Close C1;

End