NO_DATA_FOUND Vs %NOTFOUND

To see what is out in the market, you must make your resume current and apply even if you don't intent to change  the job. Why would you apply and appear for  an interview when you are not intending to make a switch? This is the best advice that I was given by my manager at a previous job and I have started practicing this mantra since 2012. First, this makes your resume current at all time, second, you will meet and get to know about the people in your areas, new technologies, projects, and their challenges. This will help you evaluate your technical skills  and focus on what you are missing.

OK, I can go on and on with the career advice but I will stop right here and get you into the topic. This is one of the question that was asked to me not to long ago? This is PL/SQL related and most DBA know PL/SQL enough to do their job, and some may know it already and for some this is going to be a review.

NO_DATA_FOUND: This is a PL/SQL pre-defined exception which is used with SELECT INTO clause. This exception is thrown when there are no records for SELECT .Checking SQL%FOUND or SQL%NOTFOUND have no meaning in case of select into statement, because if the select statement returns no rows it will always raise no_data_found exception, except, if that select statement invokes aggregate function, it will always return data or null if no rows has been selected.

SET Serveroutput ON;
DECLARE
  dept_no NUMBER(4) := 270;
BEGIN
SELECT deptno INTO dept_no FROM dept_temp WHERE deptno = 27;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Record Not Found');
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('Too Many Records Found');
END;
/

%NOTFOUND: A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

This PL/SQL block uses %FOUND to select an action.

DECLARE
   CURSOR emp_cur IS SELECT * FROM employees ORDER BY employee_id;
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cur;
   LOOP   -- loop through the table and get each employee
      FETCH emp_cur INTO emp_rec;
      IF emp_cur%FOUND THEN
         dbms_output.put_line('Employee #' || emp_rec.employee_id ||
            ' is ' || emp_rec.last_name);
      ELSE
         dbms_output.put_line('--- Finished processing employees ---');
         EXIT;
      END IF;
   END LOOP;
   CLOSE emp_cur;
END;
/

Instead of using %FOUND in an IF statement, the next example uses %NOTFOUND in an EXIT WHEN statement.

DECLARE
   CURSOR emp_cur IS SELECT * FROM employees ORDER BY employee_id;
   emp_rec employees%ROWTYPE;
BEGIN
   OPEN emp_cur;
   LOOP   -- loop through the table and get each employee
      FETCH emp_cur INTO emp_rec;
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line('Employee #' || emp_rec.employee_id ||
         ' is ' || emp_rec.last_name);
   END LOOP;
   CLOSE emp_cur;
END;
/

NO_DATA_FOUND is an exception raised when no data are found for SELECT INTO clause and %NOTFOUND is an attribute for cursor to check if the cursor has any more data left. The above examples make it clear as when to use them.