Write a script to create a PL/SQL procedure that modifies the next date of appointment of certain patient. Call the procedure from anonymous block to allow the user to enter the patient's first name, last name, date of birth and the new date of appointment. If the following data is used, the output will be as follows. Enter the first name: Lewis Enter the last name: Crow Enter the date of birth: 16-OCT-49 Enter the new appointment date: 12-Jan-2012 The data has been updated. The original next appointment date was 01-JUL-05 PL/SQL procedure successfully completed.
Based on script below , answer question in photo
CREATE TABLE DOCTOR
(DOC_ID NUMBER(3),
DOC_NAME VARCHAR2(9),
DATEHIRED DATE,
SALPERMON NUMBER(12),
AREA VARCHAR2(20),
SUPERVISOR_ID NUMBER(3),
CHGPERAPPT NUMBER(3),
ANNUAL_BONUS NUMBER(5),
CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID));
CREATE TABLE PATIENT
(PT_ID NUMBER(3),
PT_LNAME VARCHAR2(15),
PT_FNAME VARCHAR2(15),
PTDOB DATE,
DOC_ID NUMBER(3),
NEXTAPPTDATE DATE,
LASTAPPTDATE DATE,
CONSTRAINT PATIENT_PT_ID_PK PRIMARY KEY (PT_ID),
CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID)
REFERENCES DOCTOR);
INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,
'Pediatrics', 100, 75, 4500);
INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null);
INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000,
'Pediatrics', 432, 40, 2250);
INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,
'Neurology', 289, 85, 7500);
INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550,
'Family Practice', 100, 25, 2250);
INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,
'Neurology', 289, 80, 6500);
INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800,
'Orthopedics', 876, 85, 7700);
INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'), 10500,
'Orthopedics', 100, 90, 8900);
INSERT INTO DOCTOR VALUES(889, 'Thompson' , TO_DATE('18-MAR-97'), 6500,
'Rehab', 100, 65, 3200);
INSERT INTO DOCTOR VALUES(239, 'Pronger' , TO_DATE('18-DEC-99'), 3500,
'Rehab',889, 40, null);
INSERT INTO DOCTOR VALUES(289, 'Borque' , TO_DATE('30-JUN-89'), 16500,
'Neurology', 100, 95, 6500);
INSERT INTO DOCTOR VALUES(100, 'Stevenson' , TO_DATE('30-JUN-79'), 23500,
'Director', null,null,null );
INSERT INTO patient VALUES(168, 'James', 'Paul', TO_DATE('14-MAR-97'), 432,
TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(331, 'Anderson', 'Brian', TO_DATE('06-MAR-48'), 235,
TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(313, 'James', 'Scott', TO_DATE('01-MAR-33'), 235,
TO_DATE('20-JUL-03'), TO_DATE('20-JUN-03'));
INSERT INTO patient VALUES(816, 'Smith', 'Jason', TO_DATE('12-DEC-99'), 509,
TO_DATE('15-NOV-03'), TO_DATE('15-MAY-03'));
INSERT INTO patient VALUES(314, 'Porter', 'Susan', TO_DATE('14-NOV-67'), 235,
TO_DATE('01-OCT-03'), TO_DATE('01-MAR-03'));
INSERT INTO patient VALUES(315, 'Saillez', 'Debbie', TO_DATE('09-SEP-55'), 235,
TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));INSERT INTO patient VALUES(719, 'Rogers', 'Anthony', TO_DATE('07-DEC-41'), 504,
TO_DATE('01-NOV-03'), TO_DATE('01-JAN-03'));
INSERT INTO patient VALUES(264, 'Walters', 'Stephanie', TO_DATE('01-JAN-45'),
504, TO_DATE('12-DEC-03'), TO_DATE('12-DEC-02'));
INSERT INTO patient VALUES(267, 'Westra', 'Lynn', TO_DATE('12-JUL-57'), 235,
TO_DATE('02-FEB-04'), TO_DATE('02-FEB-03'));
INSERT INTO patient VALUES(103, 'Poole', 'Jennifer', TO_DATE('13-MAY-02'), 389,
TO_DATE('01-DEC-03'), TO_DATE('01-JUN-03'));
INSERT INTO patient VALUES(108, 'Baily', 'Ryan', TO_DATE('25-DEC-77'), 235,
TO_DATE('06-JUN-05'), TO_DATE('06-JUN-03'));
INSERT INTO patient VALUES(943, 'Crow', 'Lewis', TO_DATE('16-OCT-49'), 235,
TO_DATE('01-JUL-05'), TO_DATE('01-MAR-02'));
INSERT INTO patient VALUES(847, 'Cochran', 'John', TO_DATE('03-MAR-48'), 356,
TO_DATE('02-DEC-05'), TO_DATE('01-JAN-02'));
INSERT INTO patient VALUES(163, 'Roach', 'Becky', TO_DATE('08-SEP-75'), 235,
TO_DATE('01-DEC-05'), TO_DATE('01-JAN-02'));
INSERT INTO patient VALUES(504, 'Jackson', 'John', TO_DATE('14-OCT-43'), 235,
TO_DATE('21-JUL-03'), TO_DATE('10-NOV-02'));
INSERT INTO patient VALUES(809, 'Kowalczyk', 'Paul', TO_DATE('12-NOV-51'), 558,
TO_DATE('29-JUL-03'), TO_DATE('19-JUN-03'));
INSERT INTO patient VALUES(703, 'Davis', 'Linda', TO_DATE('17-JUL-02'), 509,
TO_DATE('21-JUL-03'), TO_DATE('22-MAY-03'));
INSERT INTO patient VALUES(307, 'Jones', 'J.C.', TO_DATE('17-JUL-02'), 509,
TO_DATE('21-JUL-03'), TO_DATE('22-MAY-03'));
INSERT INTO patient VALUES(439, 'Wright', 'Chasity', TO_DATE('23-APR-73'), 235,
TO_DATE(''), TO_DATE(''));
INSERT INTO patient VALUES(696, 'Vanderchuck', 'Keith', TO_DATE('08-AUG-68'),
504, TO_DATE(''), TO_DATE('15-JUN-03'));
INSERT INTO patient VALUES(966, 'Mcginnis', 'Allen', TO_DATE('03-MAY-59'), 504,
TO_DATE(''), TO_DATE('15-JUN-03'));
INSERT INTO patient VALUES(669, 'Sakic', 'Joe', TO_DATE('16-SEP-76'), 504,
TO_DATE(''), TO_DATE('15-JUN-03'));
Step by step
Solved in 2 steps
