cursor bulk : Fetch « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. Fetch

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>
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>
SQL> drop table employee;

Table dropped.