Factory working time based on shift timings

set serveroutput on
declare
mark number:=0;
dur number:=0;
days number:=1;
totaldays number:=5;
begin
while days <= totaldays loop
dbms_output.put_line('day ------------------------ '||days);
for rec in (
select * from shifts where company='c2'
) loop
    dbms_output.put_line('shift - '||rec.shift||' - '||rec.starttime||' - '||rec.endtime);
    if rec.starttime>mark then
        dbms_output.put_line('before shift start');
        dur:=rec.starttime-mark;
        dbms_output.put('down *** ');
        dbms_output.put_line(mark||' - '||rec.starttime||' - '||dur);
        mark:=rec.starttime;
       
            if rec.starttime            dbms_output.put_line('on the same day');
            dur:=rec.endtime-rec.starttime;
            dbms_output.put('up *** ');
            dbms_output.put_line(rec.starttime||' - '||rec.endtime||' - '||dur);
            mark:=rec.endtime;
           
            else
            dbms_output.put_line('goes on next day');
            dur:=24-rec.starttime;
            dbms_output.put('up *** ');
            dbms_output.put_line(rec.starttime||' - '||24||' - '||dur);
            dur:=rec.endtime;
            dbms_output.put('up *** ');
            dbms_output.put_line(0||' - '||rec.endtime||' - '||dur);
            mark:=rec.endtime;
            end if;
    elsif rec.starttime        dbms_output.put_line('on the same day');
        dur:=rec.endtime-rec.starttime;
        dbms_output.put('up *** ');
        dbms_output.put_line(rec.starttime||' - '||rec.endtime||' - '||dur);
        mark:=rec.endtime;
           
    else
        dbms_output.put_line('goes on next day');
        dur:=24-rec.starttime;
        dbms_output.put('up *** ');
        dbms_output.put_line(rec.starttime||' - '||24||' - '||dur);
        dur:=rec.endtime;
        dbms_output.put('up *** ');
        dbms_output.put_line(0||' - '||rec.endtime||' - '||dur);
        mark:=rec.endtime;       
    end if;
end loop;
days:=days + 1;
end loop;
end;



day ------------------------ 1
shift - 1 - 7 - 14
before shift start
down *** 0 - 7 - 7
on the same day
up *** 7 - 14 - 7
shift - 2 - 16 - 22
before shift start
down *** 14 - 16 - 2
on the same day
up *** 16 - 22 - 6
shift - 3 - 23 - 5
before shift start
down *** 22 - 23 - 1
goes on next day
up *** 23 - 24 - 1
up *** 0 - 5 - 5
day ------------------------ 2
shift - 1 - 7 - 14
before shift start
down *** 5 - 7 - 2
on the same day
up *** 7 - 14 - 7
shift - 2 - 16 - 22
before shift start
down *** 14 - 16 - 2
on the same day
up *** 16 - 22 - 6
shift - 3 - 23 - 5
before shift start
down *** 22 - 23 - 1
goes on next day
up *** 23 - 24 - 1
up *** 0 - 5 - 5
day ------------------------ 3
shift - 1 - 7 - 14
before shift start
down *** 5 - 7 - 2
on the same day
up *** 7 - 14 - 7
shift - 2 - 16 - 22
before shift start
down *** 14 - 16 - 2
on the same day
up *** 16 - 22 - 6
shift - 3 - 23 - 5
before shift start
down *** 22 - 23 - 1
goes on next day
up *** 23 - 24 - 1
up *** 0 - 5 - 5
day ------------------------ 4
shift - 1 - 7 - 14
before shift start
down *** 5 - 7 - 2
on the same day
up *** 7 - 14 - 7
shift - 2 - 16 - 22
before shift start
down *** 14 - 16 - 2
on the same day
up *** 16 - 22 - 6
shift - 3 - 23 - 5
before shift start
down *** 22 - 23 - 1
goes on next day
up *** 23 - 24 - 1
up *** 0 - 5 - 5
day ------------------------ 5
shift - 1 - 7 - 14
before shift start
down *** 5 - 7 - 2
on the same day
up *** 7 - 14 - 7
shift - 2 - 16 - 22
before shift start
down *** 14 - 16 - 2
on the same day
up *** 16 - 22 - 6
shift - 3 - 23 - 5
before shift start
down *** 22 - 23 - 1
goes on next day
up *** 23 - 24 - 1
up *** 0 - 5 - 5

PL/SQL procedure successfully completed.