DECLARE 
    CURSOR dept_cur 
    IS 
      SELECT deptno 
      FROM   dept 
      ORDER  BY deptno; 
    -- Employee cursor all employees for a dept number 
    CURSOR emp_cur ( v_dept_no dept.deptno%TYPE) 
    IS 
      SELECT ename 
      FROM   emp 
      WHERE  deptno = v_dept_no; 
BEGIN 
    FOR dept_rec IN dept_cur LOOP 
        dbms_output.Put_line('Employees in Department ' ||'  ' ||To_char(dept_rec.deptno)); 

        FOR emp_rec IN emp_cur(dept_rec.deptno) LOOP 
            dbms_output.Put_line('...Employee is '||'   '||emp_rec.ename); 
        END LOOP; 
    END LOOP; 
END; 

Output;
Employees in Department   30
...Employee is    BLAKE
...Employee is    ALLEN
...Employee is    WARD
...Employee is    MARTIN
...Employee is    TURNER
...Employee is    JAMES
Employees in Department   40  -->Here(don't want to print this line)

where exists (select deptno from emp)

      SELECT deptno 
      FROM   dept a
      where exists (select deptno from emp b where a.deptno=b.deptno(+))

CURSOR dept_cur IS 
SELECT deptno d
 WHERE EXISTS (SELECT 1 FROM emp e where e.deptno = d.deptno)
  FROM dept 
 ORDER BY deptno; 

SQL> DROP TABLE dept;
 
Table dropped
SQL> DROP TABLE emp;
 
Table dropped
SQL> CREATE TABLE dept(deptno NUMBER);
 
Table created
SQL> CREATE TABLE emp(empno NUMBER, deptno NUMBER);
 
Table created
SQL> INSERT INTO dept VALUES(10);
 
1 row inserted
SQL> INSERT INTO dept VALUES(20);
 
1 row inserted
SQL> INSERT INTO dept VALUES(30);
 
1 row inserted
SQL> INSERT INTO emp VALUES(1000,10);
 
1 row inserted
SQL> INSERT INTO emp VALUES(1001,10);
 
1 row inserted
SQL> INSERT INTO emp VALUES(1002,10);
 
1 row inserted
SQL> INSERT INTO emp VALUES(2000,20);
 
1 row inserted
SQL> INSERT INTO emp VALUES(2001,20);
 
1 row inserted
SQL> INSERT INTO emp VALUES(2002,20);
 
1 row inserted
SQL> COMMIT;
 
Commit complete

SQL> set serveroutput on;
SQL> 
SQL> DECLARE
  2      CURSOR dept_cur
  3      IS
  4        SELECT DEPTNO
  5    FROM DEPT
  6   WHERE deptno IN (SELECT DEPTNO FROM EMP)
  7        ORDER  BY deptno;
  8      -- Employee cursor all employees for a dept number
  9      CURSOR emp_cur ( v_dept_no dept.deptno%TYPE)
 10      IS
 11        SELECT empno
 12        FROM   emp
 13        WHERE  deptno = v_dept_no;
 14  BEGIN
 15      FOR dept_rec IN dept_cur LOOP
 16          dbms_output.Put_line('Employees in Department ' ||'  ' ||To_char(dept_rec.deptno));
 17  
 18          FOR emp_rec IN emp_cur(dept_rec.deptno) LOOP
 19              dbms_output.Put_line('...Employee is '||'   '||emp_rec.empno);
 20          END LOOP;
 21      END LOOP;
 22  END;
 23  /
 
Employees in Department   10
...Employee is    1000
...Employee is    1001
...Employee is    1002
Employees in Department   20
...Employee is    2000
...Employee is    2001
...Employee is    2002
 
PL/SQL procedure successfully completed

SQL> DECLARE 
  2      CURSOR emp_cur
  3      IS 
  4        SELECT deptno, ename,
  5               row_number() over (partition by deptno order by ename) rn
  6        FROM   emp ; 
  7  BEGIN 
  8    FOR emp_rec IN emp_cur LOOP 
  9      if emp_rec.rn = 1 then
 10        dbms_output.Put_line('Employees in Department ' ||'  ' ||To_char(emp_rec.deptno)); 
 11      end if;
 12      dbms_output.Put_line('...Employee is '||'   '||emp_rec.ename); 
 13      END LOOP; 
 14  END; 
 15  /
Employees in Department   10
...Employee is    CLARK
...Employee is    KING
...Employee is    MILLER
Employees in Department   20
...Employee is    ADAMS
...Employee is    FORD
...Employee is    JONES
...Employee is    SCOTT
...Employee is    SMITH
Employees in Department   30
...Employee is    ALLEN
...Employee is    BLAKE
...Employee is    JAMES
...Employee is    MARTIN
...Employee is    TURNER
...Employee is    WARD

PL/SQL procedure successfully completed.

SQL> SELECT 'For Department no : ' || D.DEPTNO || CHR(10) ||
  2         'Employee numbers are ' || CHR(10) || LISTAGG(EMPNO, ',' || CHR(10)) WITHIN GROUP(ORDER BY EMPNO) AS EMPLOYEE_NO
  3    FROM DEPT D, EMP E
  4   WHERE D.DEPTNO = E.DEPTNO
  5   GROUP BY D.DEPTNO;
 
EMPLOYEE_NO
--------------------------------------------------------------------------------
For Department no : 10
Employee numbers are
1000,
1001,
1002
 
For Department no : 20
Employee numbers are
2000,
2001,
2002

ORA-01705: an outer join cannot be specified on a correlation column

WHERE EXISTS (SELECT 1 FROM emp e where e.deptno = d.deptno)
                  (AND)
WHERE deptno IN (SELECT DEPTNO FROM EMP)