一、使用ROWNUM
declare
type emp_record_type is record(
record_empno scott.emp.empno%type,
record_ename scott.emp.ename%type,
record_job scott.emp.job%type,
record_mgr scott.emp.mgr%type,
record_hiredate scott.emp.hiredate%type
);
type emp_table_type is table of emp_record_typeindex by binary_integer;
emp_table emp_table_type;
c number(2);
begin
select count(*) into c from scott.emp;
for i in 1..c loop
select t2.empno,t2.ename,t2.job,t2.mgr,t2.hiredate intoemp_table(i) from (select rownum r,t1.* from scott.emp t1)t2 where r=i;
end loop;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).record_ename);
end loop;
end;
二、使用BULKCOLLECT
declare type demo_record_type is record(r_id demo.id%type,r_namedemo.name%type);
demo_record demo_record_type;
type demo_table_type is table of demo_record_type index bybinary_integer;
demo_table demo_table_type;
begin
select * bulk collect into demo_table from demo;
for i in 1..demo_table.count loop
dbms_output.put_line(demo_table(i).r_id||' '||demo_table(i).r_name);
exit when i=110;
end loop;
end;
declare
type emp_record_type is record(
record_empno scott.emp.empno%type,
record_ename scott.emp.ename%type,
record_job scott.emp.job%type,
record_mgr scott.emp.mgr%type,
record_hiredate scott.emp.hiredate%type
);
type emp_table_type is table of emp_record_typeindex by binary_integer;
emp_table emp_table_type;
c number(2);
begin
select count(*) into c from scott.emp;
for i in 1..c loop
select t2.empno,t2.ename,t2.job,t2.mgr,t2.hiredate intoemp_table(i) from (select rownum r,t1.* from scott.emp t1)t2 where r=i;
end loop;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).record_ename);
end loop;
end;
二、使用BULKCOLLECT
declare type demo_record_type is record(r_id demo.id%type,r_namedemo.name%type);
demo_record demo_record_type;
type demo_table_type is table of demo_record_type index bybinary_integer;
demo_table demo_table_type;
begin
select * bulk collect into demo_table from demo;
for i in 1..demo_table.count loop
dbms_output.put_line(demo_table(i).r_id||' '||demo_table(i).r_name);
exit when i=110;
end loop;
end;