NFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID. Expected output: Enter the patient ID: 168 The status of X-immunization : REQUIRED
NFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID. Expected output: Enter the patient ID: 168 The status of X-immunization : REQUIRED
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
INFO 2303
Assignment # : PL/SQL Procedure & Function Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID.
Expected output:
Enter the patient ID: 168
The status of X-immunization : REQUIRED
![CREATE TABLE PATIENT
(PT_ID
PT LNAME
PT FNAME
PTDOB
DOC ID
NEXTAPPTDATE
NUMBER(3),
VARCHAR2(15),
VARCHAR2(15),
DATE,
NUMBER(3).
DATE,
DATE,
LASTAPPTDATE
CONSTRAINT PATIENT_PT_ID PK PRIMARY KEY
(PT_ID),
CONSTRAINT PATIENT_DOC_ID_FK FOREIGN
KEY(DOC_ID)
REFERENCES DOCTOR);
INSERT INTO patient VALUES(168, James', "Paur.
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-0CT-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, 'Moginnis', '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')):](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F1a09c6a4-9198-45a2-829d-500ec4fd4250%2Fbf27b874-936b-40c5-8450-cbf264ed1560%2Fx5eai6_processed.jpeg&w=3840&q=75)
Transcribed Image Text:CREATE TABLE PATIENT
(PT_ID
PT LNAME
PT FNAME
PTDOB
DOC ID
NEXTAPPTDATE
NUMBER(3),
VARCHAR2(15),
VARCHAR2(15),
DATE,
NUMBER(3).
DATE,
DATE,
LASTAPPTDATE
CONSTRAINT PATIENT_PT_ID PK PRIMARY KEY
(PT_ID),
CONSTRAINT PATIENT_DOC_ID_FK FOREIGN
KEY(DOC_ID)
REFERENCES DOCTOR);
INSERT INTO patient VALUES(168, James', "Paur.
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-0CT-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, 'Moginnis', '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')):
![4. Write a function that takes patient date of birth. If the patient born
after 30h June 1990 then he/she will be required to set appointment
for immunization. Call this function from anonymous block to allow
the user to enter the patient ID.
Expected output:
Enter the patient ID: 168
The status of X-immunization : REQUIRED](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F1a09c6a4-9198-45a2-829d-500ec4fd4250%2Fbf27b874-936b-40c5-8450-cbf264ed1560%2F1dg4v8d_processed.png&w=3840&q=75)
Transcribed Image Text:4. Write a function that takes patient date of birth. If the patient born
after 30h June 1990 then he/she will be required to set appointment
for immunization. Call this function from anonymous block to allow
the user to enter the patient ID.
Expected output:
Enter the patient ID: 168
The status of X-immunization : REQUIRED
Expert Solution
![](/static/compass_v2/shared-icons/check-mark.png)
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
![Starting Out with Python (4th Edition)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
![Starting Out with Python (4th Edition)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
![C How to Program (8th Edition)](https://www.bartleby.com/isbn_cover_images/9780133976892/9780133976892_smallCoverImage.gif)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781337627900/9781337627900_smallCoverImage.gif)
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
![Programmable Logic Controllers](https://www.bartleby.com/isbn_cover_images/9780073373843/9780073373843_smallCoverImage.gif)
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education