Question
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
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.
--- 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;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started