Oracle PL/SQL Tutorial/Cursor/Explicit Cursor

explicit cursor with cursor variable

SQL>
SQL> create table myTable (x primary key) organization index as select 1 from dual;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure explicit is
  2    cursor explicit_cur is select 1 from myTable;
  3    dummy number;
  4  begin
  5    for i in 1 .. 500 loop
  6       open explicit_cur;
  7       fetch explicit_cur
  8       into dummy;
  9       close explicit_cur;
 10    end loop;
 11  end;
 12  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Use explicit cursor to fetch and store value to number variable

SQL> create table myTable(
  2     x number,
  3     y char(100)
  4  );
Table created.
SQL>
SQL> insert into myTable
  2  select rownum, "padding"
  3  from all_objects
  4  where rownum < 10001;
10000 rows created.
SQL>
SQL>
SQL>
SQL> create or replace procedure explicit is
  2    cursor explicit_cur is
  3       select 1
  4       from   myTable
  5       where  x = 1;
  6    dummy number;
  7  begin
  8    for i in 1 .. 500 loop
  9       open explicit_cur;
 10           fetch explicit_cur
 11           into dummy;
 12       close explicit_cur;
 13    end loop;
 14  end;
 15  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.