Oracle PL/SQL - Checking the status of explicit cursors

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

The following example uses cursor variables with explicit cursors.

It shows the values of cursor variables on a cursor that loops through employee names in a department.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w w w .j a v  a2s .  c o  m

Elapsed: 00:00:00.02
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL>
SQL>
SQL> Declare
  2        cursor c_emp (cin_deptNo NUMBER) is
  3           select eName
  4             from emp
  5             where deptNo=cin_deptNo;
  6        v_eName VARCHAR2(256);
  7  begin
  8        if not c_emp%ISOPEN then
  9             DBMS_OUTPUT.put_line('Cursor is closed');
 10        end if;
 11
 12        open c_emp(10);
 13
 14        if c_emp%ISOPEN then
 15             DBMS_OUTPUT.put_line('Cursor is opened');
 16        end if;
 17
 18        loop
 19            fetch c_emp into v_eName;
 20            if c_emp%NOTFOUND then
 21                 DBMS_OUTPUT.put_line('No rows to fetch!');
 22                 exit; -- the same as exit when c1%NOTFOUND;
 23            end if;
 24            DBMS_OUTPUT.put_line('Processed:'||c_emp%rowcount);
 25        end loop;
 26
 27        close c_emp;
 28        if not c_emp%ISOPEN then
 29               DBMS_OUTPUT.put_line('Cursor is closed');
 30        end if;
 31  end;
 32  /
Cursor is closed
Cursor is opened
Processed:1
Processed:2
No rows to fetch!
Cursor is closed

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>

Related Topic