SQL%notfound issue

Question

When I am executing below code it's not printing the message "Employee doesn't exist with department id" for deptno=40.

declare
    Cursor c1 is select * from dept;
    Cursor c2(p_deptno number) is select * from emp where deptno=p_deptno;
Begin
    For i in c1
    Loop
        for j in c2(i.deptno)
        loop    
            if sql%notfound then
                dbms_output.put_line('Employee doesnt exist with deartment id' || i.deptno);
            else
                dbms_output.put_line(i.deptno || ' ' || j.empno || ' ' || j.ename);
            end if;
        end loop;
    end loop;
end;
/

Output:

10 7782 CLARK
10 7839 KING
10 7934 MILLER
20 7369 SMITH
20 7566 JONES
20 7788 SCOTT
20 7876 ADAMS
20 7902 FORD
30 7499 ALLEN
30 7521 WARD
30 7654 MARTIN
30 7698 BLAKE
30 7844 TURNER
30 7900 JAMES

Contents of tables:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Answer 1

Do not do loop around a cursor within another loop around a cursor if you can at all avoid it. By doing so, you have reinvented the nested loops join, which may not be the most efficient way of joining your result sets.

Instead, you should first combine the two cursors into one. That way, the optimizer gets to choose the best way of doing the join.

In your case, you need to outer join the second cursor to the first, which means your procedure becomes:

BEGIN
  FOR i IN (SELECT d.deptno,
                   e.empno,
                   e.ename
            FROM   dept d
            LEFT   OUTER JOIN emp e
            ON     d.deptno = e.deptno)
  LOOP
    IF e.empno IS NOT NULL
    THEN
      dbms_output.put_line('Employee doesn''t exist with department id' || i.deptno);
    ELSE
      dbms_output.put_line(i.deptno || ' ' || i.empno || ' ' || i.ename);
    END IF;
  END LOOP;
END;
/

Answer 2

You loop through a list of rows:

BEGIN
    FOR j IN (SELECT * -- fake table with no rows
                FROM DUAL
               WHERE 1 = 2)
    LOOP
        DBMS_OUTPUT.put_line ('work, work..'); -- this won't happen
    END LOOP;
END;

If you don't have rows, you don't loop through anything.

You can use sql%notfound within a select into:

DECLARE
    v_tmp   NUMBER;
BEGIN
    SELECT col1
      INTO v_tmp
      FROM (SELECT 1 col1 FROM DUAL -- select nothing from a fake table
            UNION ALL
            SELECT 2 col1
              FROM DUAL
             WHERE 1 = 2 -- change to 1=1 to get a too-many-rows exception
             );
EXCEPTION
    WHEN OTHERS
    THEN
        IF (SQL%NOTFOUND)
        THEN
            DBMS_OUTPUT.put_line ('I didn''t find anything..');
        ELSE
            DBMS_OUTPUT.put_line ('We have some other exception..');
        END IF;
END;

Your solution could be to set a flag and check it after your inner-loop:

DECLARE
    CURSOR c1
    IS
        SELECT * FROM dept;

    CURSOR c2 (p_deptno NUMBER)
    IS
        SELECT *
          FROM emp
         WHERE deptno = p_deptno;

    v_found   BOOLEAN;
BEGIN
    FOR i IN c1
    LOOP
        v_found := FALSE; -- will be set when we find something..

        FOR j IN c2 (i.deptno)
        LOOP
            v_found := TRUE; -- we found something!

            DBMS_OUTPUT.put_line (
                i.deptno || ' ' || j.empno || ' ' || j.ename);
        END LOOP;

        IF (NOT v_found) -- check if we did find i.deptno
        THEN
            DBMS_OUTPUT.put_line (
                'Employee doesnt exist with deartment id' || i.deptno);
        END IF;
    END LOOP;
END;