SQL>
SQL> create table employee (
2 id number,
3 employee_type_id number,
4 external_id varchar2(30),
5 first_name varchar2(30),
6 middle_name varchar2(30),
7 last_name varchar2(30),
8 name varchar2(100),
9 birth_date date,
10 gender_id number);
Table created.
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
2 cursor c_worker(aiv_last_name in employee.last_name%TYPE) is
3 select first_name
4 from employee
5 where last_name like aiv_last_name||'%'
6 order by id;
7
8 TYPE c_employee_table is table of c_worker%ROWTYPE
9 index by binary_integer;
10
11 t_worker c_employee_table;
12
13 begin
14 open c_worker('DOE');
15 loop
16 fetch c_worker bulk collect into t_worker limit 2;
17
18 exit when t_worker.count = 0;
19
20 for i in t_worker.first..t_worker.last loop
21 DBMS_OUTPUT.PUT_LINE(t_worker(i).first_name);
22 end loop;
23 end loop;
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.