本文转自：http://*.com/questions/9104153/what-is-the-correct-way-to-deal-with-this-oracle-ora-01403-no-data-found-except

This leads to the familiar ORA-01403: no data found exception. I tried to change the SP so that it would return NULL record in this case - the same sort of result you'd get it a query couldn't find any records - but to no avail. I am doing something wrong here.

PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet)IS

  EMAIL_ID INTEGER;BEGINSELECT id INTO EMAIL_ID FROM(SELECT id, is_replied_to, is_being_worked, date_received

           FROM SSQ_EMAILS

           WHERE is_replied_to =0AND is_being_worked =0ORDERBY date_received ASC)WHERE rownum =1;UPDATE SSQ_EMAILS x

             SET x.is_being_worked =1,

                 x.agent_id = pAgentId,

                 x.work_started_date = SYSDATE

             WHERE x.id = EMAIL_ID;OPEN pRecs FORSELECT x.id,

             x.message_id,

             x.to_email,

             x.from_email,

             x.subject,

             x.message,

             x.date_received,

             x.href_link,

             x.is_being_worked,

             x.work_started_date,

             x.is_replied_to

        FROM SSQ_EMAILS x

        WHERE x.id = EMAIL_ID;

        EXCEPTION

          WHEN no_data_found

            THEN OPEN pRecs FOR SELECT NULL FROM SSQ_EMAILS;
       END;

答：

Solved it by doing this:

EXCEPTION

   WHEN no_data_found THEN
　　　　OPEN pRecs FOR
　　　　　　SELECT NULL FROM SSQ_EMAILS  s

      WHERE s.id ISNULL;

It works because the RefCursor has to be opened.

I need an empty result, and this seems like a safe way to guarantee that, because the ID is the PK and cannot be null.