sqloracleplsqloracle11goracle19c

How to write a condition to do something where a for loop returns no rows


In this loop I would like to capture the function not returning data in the loop to execute an else command or an exception when there is no data. But this does not work. Please help me out with the correct syntax or a work around thanks!!

begin
    --looping 
    for i in (
    select x, y, z, rownum
        from period
    where x = 0   -- here this query does not return a row
                
    ) loop 

    begin
      if sql%rowcount >=1  -- tried row count 
        then 
        dbms_output.put_line ('blablabla');
        
        else
        dbms_output.put_line ('blueeeeeee');
        end if;
        
        exception when no_data_found  --tried this exception
        then 
        dbms_output.put_line ('black');
        end;
        end loop;
end;

Solution

  • If your query in for-loop doesn't return rows, you will never get to body of this loop, so you should use either use open-cursor-fetch-close or manual checks like:

    declare
       loop_flag boolean:=false;
       loop_n   int:=0;
    begin
        --looping 
        for i in (
        select x, y, z, rownum
            from period
        where x = 0   -- here this query does not return a row
        ) 
        loop 
            -- processing fetched row:
            loop_n:=loop_n+1;
            loop_flag:=true;
            if loop_n = 1  
            then
                dbms_output.put_line ('first row: ' || r.x );
            else
                dbms_output.put_line ('other rows...');
            end if;
        end loop;
        if not loop_flag then 
            dbms_output.put_line ('no data found...');
        end;
    end;