stored procedures - No data found in oracle

Question

I have a procedure in oracle that looks like this

create or replace procedure check_display_stock as 

id_brg number(20);
rowcount number (20);

begin

  -- 1. insert datas from display into temp_display
  insert into temp_display
  select id_barang,stok,min_stok
  from display
  where stok <= min_stok;
  
  --2. select the number of datas in temp_display
  select count(rownum)
  into rowcount
  from temp_display;
  
  while(rowcount != 0) 
  loop
      
      -- Error: no data found               
      select id_barang
      into id_brg
      from temp_display
      where rownum = 1;               
      
      --just another procedure to do other things
      insert_spb(id_brg); 
      delete from temp_display where rownum = 1;
      
      end if;
          
  end loop;
  
end check_display_stock;

An error occurs when I tried to select into that says no data found. I don't understand why this happened.

Answer 1

You never decrement rowcount so you will end up deleting the rows in temp_display one-by-one and then keep going (potentially forever) and on the next iteration after you have already emptied the table you will try to select id_barang into id_brg ... and it will fail as you have already emptied the table.

Instead, you could use BULK COLLECT INTO or a CURSOR to bypass the temporary table:

create or replace procedure check_display_stock
as 
begin
  FOR cur IN ( select id_barang,
                      stok,
                      min_stok
               from   display
               where  stok <= min_stok
             )
  LOOP
    insert_spb(cur.id_barang); 
  END LOOP;
END;
/

db<>fiddle here