Please create a PL/SQL procedure that checks availability of a doctor. The procedure has three input parameters: the doctors name, the start_time and length of a new appointment (the length is interval day to second type), and checks whether the doctor has any existing appointment that overlaps with new appointment. If there is no overlap, the procedure sets the output parameter to 1. Otherwise, the procedure sets the output to 0. Please do not count canceled appointments when checking overlap.

Please also write an anonymous PL/SQL program to call this procedure.

E.g., if the input is 'Dr. Rao', timestamp '2018-2-1 09:15:00.00', and interval '30' minute (i.e., some patient wants to see Dr. Rao from 9:15 am to 9:45 am on Feb 1, 2018). Since there are appointments from 9 to 9:30 and 9:30 to 9:40 for Dr. Rao, this procedure will set output parameter to 0 (there is overlap). If the input is 'Dr. Rao', timestamp '2018-2-1 10:00:00.00', and interval '30' minute, the procedure set the output to 1 because there is no conflict.

--- sample code to create the tables

drop table appointment_service cascade constraints;

drop table appointment cascade constraints;

drop table service cascade constraints;

drop table patient cascade constraints;

drop table doctor cascade constraints;

create table doctor

(did int,

dname varchar(50),

primary key(did));

insert into doctor values(1,'Dr. Rao');

insert into doctor values(2,'Dr. Adam');

insert into doctor values(3,'Dr. Smith');

create table patient

(pid int,

pname varchar(50),

pphone varchar(20),

primary key(pid));

insert into patient values(1,'Susan', '410-456-1122');

-- son of susan

insert into patient values(2,'Nathan','410-456-1122');

insert into patient values(3,'Ella', '410-456-8876');

insert into patient values(4,'Carl', '410-456-8876');

create table service

(sid int,

sdecription varchar(200),

primary key(sid));

insert into service values(1,'office visit');

insert into service values(2,'flu shot');

insert into service values(3,'physical exam');

create table appointment

(aid int,

pid int,

did int,

reason_for_visit varchar(200),

start_time timestamp, end_time timestamp,

status int, -- 1 booked, 2 happended, 3 canceled

primary key(aid),

foreign key (pid) references patient,

foreign key (did) references doctor);

-- done

insert into appointment values(1,1,1,'flu and low fever', timestamp '2018-2-1 9:00:00.00',

timestamp '2018-2-1 9:30:00.00',2);

-- done

insert into appointment values(2,2,1,'flu shot', timestamp '2018-2-1 9:30:00.00',

timestamp '2018-2-1 9:40:00.00',2);

-- canceled

insert into appointment values(3,3,2,'annual physical', timestamp '2018-2-1 9:00:00.00',

timestamp '2018-2-1 9:30:00.00',3);

-- booked

insert into appointment values(4,4,2,'annual physical', timestamp '2018-2-1 16:00:00.00',

timestamp '2018-2-1 16:30:00.00',1);

-- booked

insert into appointment values(5,1,1,'follow up', timestamp '2018-2-3 12:00:00.00',

timestamp '2018-2-3 12:30:00.00',1);

-- booked

insert into appointment values(6,3,2,'annual physical', timestamp '2018-2-3 9:00:00.00',

timestamp '2018-2-3 9:30:00.00',1);

insert into appointment values(7,3,2,'flu like symptom', timestamp '2018-2-3 11:00:00.00',

timestamp '2018-2-3 11:30:00.00',1);

-- these are fictitious appointment testing new appointment

insert into appointment values(8,2,1,'follow up', timestamp '2018-2-3 09:00:00.00',

timestamp '2018-2-3 12:00:00.00',1);

insert into appointment values(9,2,1,'follow up', timestamp '2018-2-3 12:30:00.00',

timestamp '2018-2-3 16:30:00.00',1);

create table appointment_service

(

aid int,

sid int,

primary key(aid, sid),

foreign key(aid) references appointment,

foreign key(sid) references service

);

-- 1: office visit, 2: flu shot, 3 physical

insert into appointment_service values(1, 1);

insert into appointment_service values(2, 2);

insert into appointment_service values(4, 2);

insert into appointment_service values(4, 3);

insert into appointment_service values(5, 1);

insert into appointment_service values(6, 2);

insert into appointment_service values(6, 3);

insert into appointment_service values(7, 1);

insert into appointment_service values(8, 1);

insert into appointment_service values(9, 1);

commit;