Oracle PL/SQL - Placing cursors in nested loops

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Description

Placing cursors in nested loops

Demo

SQL>
SQL> drop table dept;
SQL> create table dept(
  2    deptno number(2,0),
  3    dname  varchar2(14),--   w w w  .  j av a2 s  .  c om
  4    loc    varchar2(13),
  5    constraint pk_dept primary key (deptno)
  6  );

Table created.
SQL>
SQL> insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
SQL> insert into dept values(20, 'RESEARCH', 'DALLAS');
SQL> insert into dept values(30, 'SALES', 'CHICAGO');
SQL> insert into dept values(40, 'OPERATIONS', 'BOSTON');
SQL>
SQL>
SQL> drop table emp;

Table dropped.

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> insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
SQL>
SQL>
SQL> declare
  2       cursor c_dept is
  3         select *
  4            from dept;
  5       r_dept c_dept%ROWTYPE;
  6       cursor c_empInDept (cin_deptNo NUMBER) is
  7         select *
  8            from emp
  9            where deptNo = cin_deptNo;
 10
 11       r_emp c_empInDept%ROWTYPE;
 12  begin
 13       open c_dept;
 14       loop
 15            fetch c_dept into r_dept;
 16            exit when c_dept%NOTFOUND;
 17              --do something with each department
 18            open c_empInDept (r_dept.deptNo);
 19            loop
 20                 fetch c_empInDept into r_emp;
 21                 exit when c_empInDept%NOTFOUND;
 22                 -- do something with each employee
 23            end loop;
 24            close c_empInDept;
 25
 26       end loop;
 27       close c_dept;
 28  end;
 29  /

PL/SQL procedure successfully completed.

Related Topic