Time Schedule Procedure

 CREATE OR REPLACE PROCEDURE time_schedule (in_SCHEDULE_ID NUMBER)

IS
   l_startdt                DATE;
   l_SCHEDULE_ID            NUMBER;
   l_DOCTOR_ID              NUMBER;
   l_SCHEDULE_DT            DATE;
   l_FROM_TIME              DATE;
   l_TO_TIME                DATE;
   l_PERIOD_OF_TIME         NUMBER;
   l_SCHEDULE_SLOT_ID       NUMBER;
   l_loop_END_TIME          DATE;
BEGIN
   SELECT SCHEDULE_ID,
          DOCTOR_ID,
          SCHEDULE_DT,
          FROM_TIME,
          TO_TIME,
          PERIOD_OF_TIME
     INTO l_SCHEDULE_ID,
          l_DOCTOR_ID,
          l_SCHEDULE_DT,
          l_FROM_TIME,
          l_TO_TIME,
          l_PERIOD_OF_TIME
     FROM DOCTOR_SCHEDULE
    WHERE SCHEDULE_ID = in_SCHEDULE_ID;

   l_startdt := l_FROM_TIME;

   WHILE l_startdt < l_TO_TIME AND l_FROM_TIME < l_TO_TIME
   LOOP
      SELECT SCHEDULE_SLOT_ID_SEQ.NEXTVAL
        INTO l_SCHEDULE_SLOT_ID
        FROM DUAL;


      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_loop_END_TIME := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);

--(l_PERIOD_OF_TIME / 24 / 60) its converted number data to a part of a day.

--that is the exact of period of time.

      END IF;

      INSERT INTO DOCTOR_SCHEDULE_SLOT (SCHEDULE_SLOT_ID,
                                        SCHEDULE_ID,
                                        DOCTOR_ID,
                                        SCHEDULE_DT,
                                        START_TIME,
                                        END_TIME)
           VALUES (l_SCHEDULE_SLOT_ID,
                   l_SCHEDULE_ID,
                   l_DOCTOR_ID,
                   l_SCHEDULE_DT,
                   l_startdt,
                   l_loop_END_TIME);

      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_startdt := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;



      COMMIT;
   END LOOP;
END;
/
------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE time_schedule (in_SCHEDULE_ID NUMBER:=1,in_doctor_id number:=104)
IS
   l_startdt                DATE;
   l_SCHEDULE_ID            NUMBER;
   l_DOCTOR_ID              NUMBER;
   l_SCHEDULE_DT            DATE;
   l_FROM_TIME              DATE;
   l_TO_TIME                DATE;
   l_PERIOD_OF_TIME         NUMBER;
   l_SCHEDULE_SLOT_ID       NUMBER;
   l_loop_END_TIME          DATE;
   
   v number;
BEGIN
select count(1) into v from  DOCTOR_SCHEDULE_SLOT where SCHEDULE_ID = in_SCHEDULE_ID and doctor_id=in_doctor_id;

if v=0 then 

   SELECT SCHEDULE_ID,
          DOCTOR_ID,
          SCHEDULE_DT,
          FROM_TIME,
          TO_TIME,
          PERIOD_OF_TIME
     INTO l_SCHEDULE_ID,
          l_DOCTOR_ID,
          l_SCHEDULE_DT,
          l_FROM_TIME,
          l_TO_TIME,
          l_PERIOD_OF_TIME
     FROM DOCTOR_SCHEDULE
    WHERE SCHEDULE_ID = in_SCHEDULE_ID;

   l_startdt := l_FROM_TIME;

   WHILE l_startdt < l_TO_TIME AND l_FROM_TIME < l_TO_TIME
   LOOP
      SELECT SCHEDULE_SLOT_ID_SEQ.NEXTVAL
        INTO l_SCHEDULE_SLOT_ID
        FROM DUAL;


      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_loop_END_TIME := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;

      INSERT INTO DOCTOR_SCHEDULE_SLOT (SCHEDULE_SLOT_ID,
                                        SCHEDULE_ID,
                                        DOCTOR_ID,
                                        SCHEDULE_DT,
                                        START_TIME,
                                        END_TIME)
           VALUES (l_SCHEDULE_SLOT_ID,
                   l_SCHEDULE_ID,
                   l_DOCTOR_ID,
                   l_SCHEDULE_DT,
                   l_startdt,
                   l_loop_END_TIME);

      IF NVL (l_PERIOD_OF_TIME, 0) > 0
      THEN
         l_startdt := l_startdt + (l_PERIOD_OF_TIME / 24 / 60);
      END IF;



      COMMIT;
   END LOOP;
   else 
   return;
   end if;
END;
/