oracle using bulk collect to achieve cursor batch fetch sql statement
- 2020-06-12 10:51:05
- OfStack
The chances of using batch fetch in 1-like situations are not that great, but Oracle does offer this feature and it's best to familiarize yourself with 1 if you ever need to use it.
From the above column you can see that if there are many columns, it may seem tedious to define a set for each column. You can combine the set with %rowtype 1.
You can use both the count attribute of the collection and the first and last attributes in the output. Another thing to note when referring to content of type %rowtype is v_depart(i).depart_code instead of v_depart.depart_code (i).
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;
From the above column you can see that if there are many columns, it may seem tedious to define a set for each column. You can combine the set with %rowtype 1.
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 both the count attribute of the collection and the first and last attributes in the output. Another thing to note when referring to content of type %rowtype is v_depart(i).depart_code instead of v_depart.depart_code (i).
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;