By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table borrower(
roll_no number,
date_of_issue date,
name_of_book varchar(20),
status varchar(10)
);
create table fine(
roll_no number,
current_date date,
amount number
);
BEGIN
insert into borrower values(1,to_date('02-JAN-2022'),'dbms','issued');
insert into borrower values(2,to_date('10-JAN-2022'),'cns','issued');
insert into borrower values(3,to_date('17-JAN-2022'),'spos','issued');
insert into borrower values(4,to_date('26-JAN-2022'),'toc','issued');
insert into fine values(1,to_date('14-FEB-2022'),null);
insert into fine values(2,to_date('14-FEB-2022'),null);
insert into fine values(3,to_date('14-FEB-2022'),null);
insert into fine values(4,to_date('14-FEB-2022'),null);
END;
/
1 rows affected
DECLARE
roll_counter number:=1;
initial_date date;
final_date date;
date_calc number;
BEGIN
FOR roll_counter IN 1 .. 4 LOOP
select date_of_issue
into initial_date
from borrower
where roll_no=roll_counter;
select current_date
into final_date
from fine
where roll_no=roll_counter;
date_calc:=final_date-initial_date;
dbms_output.put_line(date_calc);
END LOOP;
END;
/
1 rows affected
dbms_output:
246.776168981481481481481481481481481481
238.776168981481481481481481481481481481
231.776168981481481481481481481481481481
222.776168981481481481481481481481481481
DECLARE
roll_counter number:=1;
initial_date date;
final_date date;
date_calc number;
BEGIN
FOR roll_counter IN 1 .. 4 LOOP
select date_of_issue
into initial_date
from borrower
where roll_no=roll_counter;
select fine.current_date
into final_date
from fine
where roll_no=roll_counter;
date_calc:=final_date-initial_date;
dbms_output.put_line(date_calc);
END LOOP;
END;
/
1 rows affected
dbms_output:
43
35
28
19
BEGIN
FOR r IN (
SELECT f.current_date - b.date_of_issue AS diff
FROM borrower b
FULL OUTER JOIN fine f
ON (b.roll_no = f.roll_no)
WHERE COALESCE(b.roll_no, f.roll_no) BETWEEN 1 AND 4
ORDER BY COALESCE(b.roll_no, f.roll_no)
) LOOP
dbms_output.put_line(r.diff);
END LOOP;
END;
/
1 rows affected
dbms_output:
43
35
28
19