How to check the length of a table and the make an if condition with it and delete a row after?-CodePudding

I have a table that i decrease one row each time it gets in a loop. How can check before the loop if the tables length is zero and delete the row i got after?

declare
l_temp number;
begin
insert into l_tem(select toy_id from toys);
 if l_temp > 0 then 
     for rw in (select toy_id from( select toy_id   
                                   from toys
                                    order by dbms_random.value)
                                 where rownum =1 )
       loop
        dbms_output.put_line(toy_id);
       delete from toys where toy_id(rw);
     end loop;
 else
 dbms_output.put_line('no more toys');
end if;
end;

CodePudding user response：

I don't quite understand why would you do it that way, instead of simply

delete from toys;

but never mind me.

You don't have to check anything. If table is empty, FOR loop won't run any cycles and simply step out of the loop.

If you must check it, then it is

select count(*) into l_temp from toys;

Also, you should reference values using cursor variable AND column name, e.g.

delete from toys where toy_id = rw.toy_id;
                                ---------
                                this