Need help with a loop

In a 10g Db I have these 3 tables Appoint, ptprod and producer. The appoint tbl has all patients appointments and the producer table holds all the providers we have, and the ptprod tbl holds the patient and the provider the patient is assigned to. I need to write something that looks to see if a patient has an apppointment in a certian clinic for the day and if so assign them to certian providers. I need a loop because there could be more then one patient and there are several providers available. I am kind of stuck at were to start.

Code:

---

select "Patient" from appoint where "Clinic"='760' and "Date"=trunc(sysdate)

---

shows me all the patients that have an appointment that day,
and

Code:

---

select "Producer" from producer where "PGroup"='SED' and "Inactive"='0'

---

returns the provider numbers for all available providers
and

Code:

---

INSERT INTO ptprod ("Patient", "Producer", "StartDate", "EndDate",
          "IsRestricted", "IsPrimary", "Discipline", "NeedReq", "Id",
    "CreateDate", "User", "StationId", "Deleted", "Note")
  VALUES ( '783968', 'Z8922', trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
    PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ')

---

is the insert statement I need to do for each patient and for each provider. this is just one example from one of the patients for one of the providers. I need to do the above insert several times for this one patient for each provider then again for a differnet patient.

I was thinking the loop would do this, but I am stuck at how to begin. I searching the web for syntax and examples but I thought I would through my question out here. any help would be greatly appreciated.

i'm probably a little slow today but I don't totally understand how Providers are assigned to specific Appointments.

Either way - if a loop is really needed - I would probably load all appointments into a cursor then loop the cursor assigning Providers as needed.

PAVB,
thanks so much for the reply. you got me started and I really appreciate it. You're not slow I must not have expained myself well. the producer doesn't need to be assigned to appoinments but to the patients that have the appointment. either way this is the code I wrote

Code:

---

declare
prod1 CHAR(10 Byte) ;
prod2 CHAR(10 Byte) ;
cursor pat_cursor is
    select "Patient" from appoint where "Clinic"='760' and "Date"=trunc(sysdate);
pat_val pat_cursor%ROWTYPE;
begin
prod1 := 'Z8922';
prod2 := 'Z8970';
open pat_cursor;
loop
fetch pat_cursor into pat_val;
exit when pat_cursor%NOTFOUND;
INSERT INTO ptprod  VALUES ( pat_val."Patient", prod1, trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
    PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ');
INSERT INTO ptprod  VALUES ( pat_val."Patient", prod2, trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
    PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ');   
    end loop;
close pat_cursor;
end;
/

---

I have 2 issues with the code though.
1- it is only working for one patient and it should be working for 2 patients. the loop doesn't seem to be working.
2- I have hard coded two producers (Z8922 and Z8970). this works for now however the providers change every year and I don't want to keep changing the code as we get new doctors. do I need a second cursor for the producers?

There is an issue with the LOOP statement, you may want to use it with a WHILE and also include and END LOOP statement to close the loop - like...

Code:

---

WHILE condition LOOP
  statement1;
  statement2;
  ...
END LOOP;

---

PAVB,
once again I really appreciate the help. So I took your suggestion and worked out code using a while loop. Ran it, and when I checked it was still only updating one patient. I said this is crazy. turns out the way I was verifing if the code worked was selecting from the table I was updating, where the patient was the 2 patient #'s that should be getting updated. well upon further review I realized I had a typo in one of the patient numbers. It did update both patients. I re-ran my original code to see If my original code worked and that worked to. I feel bad I didn't catch this sooner. Sorry I wasted your time. However I have it with the providers hard coded and I don't want it like that. I still want to modify it to loop through the providers, so I will keep tweaking it. Thanks and sorry again