title: Oracle implicit cursor: for-else
body: <p>This is a very basic question, but what's the best way of writing a <code>FORELSE</code> into an implicit cursor in Oracle PL/SQL?  Some programming languages permit this kind of syntax, executing the <code>FORELSE</code> block when the cursor returns no rows.</p>
  
  <p>What I want to achieve is something along the following lines (pseudo-code):</p>
  
  <pre><code>DECLARE
      CURSOR test_cur IS
          SELECT 'a'
          FROM   table_with_zero_or_more_data;
  BEGIN
      FOR r_ IN test_cur LOOP
          Dbms_Output.Put_Line ('One extra row found');
      FORELSE
          Dbms_Output.Put_Line ('No data found');
      END LOOP;
  END;
  </code></pre>
  
  <p>I've tried using exceptions as follows, but the <code>NO_DATA_FOUND</code> exception doesn't get triggered by an implicit cursor.</p>
  
  <pre><code>DECLARE 
      CURSOR test_cur IS
          SELECT 'a'
          FROM   table_with_zero_or_more_data;
  BEGIN
      BEGIN
          FOR r_ IN test_cur LOOP
              Dbms_Output.Put_Line ('One extra row found');
          END LOOP;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
              Dbms_Output.Put_Line ('No data found');
      END;
  END;
  </code></pre>
  
  <p>The following does work of course (and there are similar variations, I have no doubt). But I just find this way a bit clunky. When the code becomes somewhat more real-life and verbose, it's not so obvious/intuitive that the <code>IF</code> statement is linked to the <code>FOR</code> loop. It also forces the introduction of a dummy variable, and just isn't syntactically beautiful.</p>
  
  <pre><code>DECLARE 
      CURSOR test_cur IS
          SELECT 'a'
          FROM   table_with_zero_or_more_data;
      i NUMBER := 0;
  BEGIN
      FOR r_ IN test_cur LOOP
          i := i + 1;
          Dbms_Output.Put_Line ('One extra row found');
      END LOOP;
      IF i = 0 THEN
          Dbms_Output.Put_Line ('No data found');
      END IF;
  END;
  </code></pre>
  
  <p>I just wonder if there's a better way, which is more intuitive and integrates the <code>FOR</code> and <code>IF</code> conditions?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>In case I wasn't clear enough, the point of this is to do it inside an IMPLICIT cursor.  I know I can use EXplicit cursors and check for <code>%NOTFOUND</code> etc. (actually <code>%ROWCOUNT</code> is better).</p>