While loops checking with time

Question

Just wondering if you could help me with a question. I have been told to write a function or anonymous block that will run until you put an item in a table/ The programme should check every 5 seconds for an entry. It should output a message informing you of the fact that a check took place. Once the entry is found it will inform you that it found the entry, remove the entry and then exit.

I have been trying to complete this for ages now, but no luck.

Any help would be very appreciated.

Thank you.

You must use a while loop and to aid you the 5 seconds may be controlled by calling dbms_lock.sleep(5);

What's the part causing you difficulty? Checking for a record? Outputting the message? — Dan Bracuk, Sep 18 '15 at 14:22
@TonyAndrews I literally have the opening and the end bit about outputting. — Arianna.Ellen, Sep 18 '15 at 14:28
@DanBracuk Checking the record mainly, I think i have the second part figured out. — Arianna.Ellen, Sep 18 '15 at 14:29

score 0 · Accepted Answer · answered Sep 18 '15 at 14:29

0

I don't have access to Oracle DB, but here's a general concept of how it could work (MySQL)

create table tt_mess_around (`id` INT, `name` VARCHAR(50));

delimiter \\
create procedure watcher ()

BEGIN
declare foundInsert INT default 0;
   while foundInsert = 0 do
      select id from tt_mess_around limit 1 into foundInsert;
      if (foundInsert = 0) then
         select 'sleeping for 5 seconds' from dual;
         do sleep(5);
      end if;
   end while;
   delete from tt_mess_around where id = foundInsert;
   select 'just deleted record' from dual;
END
\\
delimiter ;


call watcher;

insert into tt_mess_around 
select 1, 'Arianna' from dual;

answered Sep 18 '15 at 14:29

Uncle Iroh

5,748
6
48
61

Thank you so much, this looks like what i needed! – Arianna.Ellen Sep 18 '15 at 14:38
@Arianna.Ellen -- Remember this is MySQL though, not Oracle -- the syntax won't be a perfect match... – Uncle Iroh Sep 18 '15 at 14:39
Thank you :) I will ammend the syntax and give it a go – Arianna.Ellen Sep 18 '15 at 14:46

While loops checking with time

1 Answers1