Open cursor with parameter : Cursor Parameter « Cursor « Oracle PL/SQL Tutorial

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

SQL>
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      v_first_name employee.first_name%TYPE;
  9
 10  begin
 11    open c_worker('DOE');
 12    loop
 13      fetch c_worker into v_first_name;
 14
 15      if c_worker%notfound then
 16        close c_worker;
 17        exit;
 18      end if;
 19
 20      DBMS_OUTPUT.PUT_LINE(v_first_name);
 21    end loop;
 22  end;
 23  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employee;

Table dropped.