SQL cursor in procedure

Question

I'd like to write a procedure with cursor, which will thirl patient to first free doctor (who don't have any visit at this time, but I don't know what would be easier

• to declare hour like 'IN' in procedure and then find a free doctor at this time
• or to find a free doctor from this time to the close of medical centre knowing that one visit last 15 minutes).

Any hints how to do that?

Below is my table structure. And if you think my example is not good for procedure with function you can give me an example of different (because it must by ANY procedure with function related to my Medical Centre).

I've got four tables

• Patient (int Id_patient, char(11) name, char(20) surname, char(30) address)
• Doctor (int Id_doctor, char(11) name, char(20) surname)
• Visit (#Id_patient, #Id_doctor, Id_visit, char(20) illness, time time_of_visit)
• Work_hour (#Id_doctor, int Id_Workhour, char(11) name_of_day, time from_hour, time to_hour)

Answer 1

Something like this?

create or replace procedure sho_doc(p_hour in varchar2)
is
  cursor get_free_time_cur
  is
  select doctor_name
    from doctor_table
   where status = 'FREE'
     and slot = p_hour_in;
begin
   for idx in get_free_time_cur
   loop
      //Do some thing here
   end loop
end;

I am assuming you are using Oracle. I don't know the table structure. So, just assuming things.