SQL> declare
2 cursor cur1 is select deptno from dept;
3 cursor cur2(p1 emp.deptno%type) is select ename from emp where deptno=p1;
4 v_deptno dept.deptno%type;
5 v_name emp.ename%type;
6 begin
7 open cur1;
8 loop
9 fetch cur1 into v_deptno;
10 exit when cur1%notfound;
11 dbms_output.put_line('* * * ' ||v_deptno);
12 open cur2(v_deptno);
13 loop
14 fetch cur2 into v_name;
15 exit when cur2%notfound;
16 dbms_output.put_line('* * * * * * * ' || v_name);
17 end loop;
18 close cur2;
19 end loop;
20 close cur1;
21 end;
22 /
* * * 10
* * * * * * * CLARK
* * * * * * * KING
* * * * * * * MILLER
* * * 20
* * * * * * * SMITH
* * * * * * * JONES
* * * * * * * SCOTT
* * * * * * * ADAMS
* * * * * * * FORD
* * * 30
* * * * * * * ALLEN
* * * * * * * WARD
* * * * * * * MARTIN
* * * * * * * BLAKE
* * * * * * * TURNER
* * * * * * * JAMES
* * * 40
PL/SQL procedure successfully completed
SQL>
2 cursor cur1 is select deptno from dept;
3 cursor cur2(p1 emp.deptno%type) is select ename from emp where deptno=p1;
4 v_deptno dept.deptno%type;
5 v_name emp.ename%type;
6 begin
7 open cur1;
8 loop
9 fetch cur1 into v_deptno;
10 exit when cur1%notfound;
11 dbms_output.put_line('* * * ' ||v_deptno);
12 open cur2(v_deptno);
13 loop
14 fetch cur2 into v_name;
15 exit when cur2%notfound;
16 dbms_output.put_line('* * * * * * * ' || v_name);
17 end loop;
18 close cur2;
19 end loop;
20 close cur1;
21 end;
22 /
* * * 10
* * * * * * * CLARK
* * * * * * * KING
* * * * * * * MILLER
* * * 20
* * * * * * * SMITH
* * * * * * * JONES
* * * * * * * SCOTT
* * * * * * * ADAMS
* * * * * * * FORD
* * * 30
* * * * * * * ALLEN
* * * * * * * WARD
* * * * * * * MARTIN
* * * * * * * BLAKE
* * * * * * * TURNER
* * * * * * * JAMES
* * * 40
PL/SQL procedure successfully completed
SQL>