传统的fetch into一次只能取得一条数据,使用fetch bulk collect into可以一次从游标中取得所有数据,使用limit子句可以限制一次取的数据条数
1、fetch bulk collect into
begin
declare
cursor c_dept is select * from dept;
type dept_record is table of dept%rowtype;
v_dept dept_record;
begin
open c_dept;
fetch c_dept bulk collect into v_dept;
close c_dept;
for i in 1.. v_dept.count loop
dbms_output.put_line('部门名称:'||v_dept(i).dname||' 部门地址:'||v_dept(i).loc);
end loop;
end;
end;
2、使用limit子句限制提取的行数
begin
declare
cursor c_dept is select * from dept;
type dept_record is table of dept%rowtype;
v_dept dept_record;
begin
open c_dept;
loop
exit when c_dept%NOTFOUND;
fetch c_dept bulk collect into v_dept limit 3;
dbms_output.put_line('-----------------------------------------------------------');
for i in 1.. v_dept.count loop
dbms_output.put_line('部门名称:'||v_dept(i).dname||' 部门地址:'||v_dept(i).loc);
end loop;
end loop;
close c_dept;
end;
end;